Text in column causing SUMPRODUCT error

[Greg Snidow]

Greegings. I have a SUMPRODUCT formula that is having errors when one of the
columns has text instead of a NULL or a number. If I delete the text cells
in that column it works as desired. I'll give a simple example. Suppose I
have the following in A1:B6....

a 1
a 2
a abc
b 1
b 1
a 2

And I need this...

=SUMPRODUCT(($A$1:$A$6="a")*($B$1:$B$6))

It errors out until I delete the "abc" in cell B3, then it works as desired.
I tried to replace the "abc" with a 0 by trying this...

=IF(ISNUMBER(B3)=FALSE,0,B3)

And it works for that particular cell, but it does not work in the
SUMPRODUCT formula like this...

=SUMPRODUCT(($A$1:$A$6="a")*(IF(ISNUMBER($B$1:$B$6)=FALSE,0,$B$1:$B$6))*($B$1:$B$6))

Any ideas on how I can deal with the text cells in column "B"? Deleting
them is not an option. Thank you.

 

[Dave Peterson]

try:

=SUMPRODUCT(--($A$1:$A$6="a"),$B$1:$B$6)

Just like =sum() will treat text as 0, =sumproduct() with the comma syntax will
do the same.

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Rick Rothstein]

This array-entered** formula appears to do what you want...

=SUM(($A$1:$A$6="a")*IF(ISNUMBER($B$1:$B$6),$B$1:$B$6,0))

**Commit formula using Ctrl+Shift+Enter and not just Enter by itself

 

[Paul C]

Try changing you formula to this

=SUMPRODUCT(--($A$1:$A$6="a"),$B$1:$B$6)

The double operator -- forces an evaluation and replacing the * with a ,
forces each array to evaluate seperately

 

[Greg Snidow]

Please note, I am trying all of your suggestions, with Paul's and Dave's
being the same, so I am replying to myself simply to consolidate the thread.
First off, this is the real formula from my spreadsheet, and the one giving
me errors...

=SUMPRODUCT(($A$23:$A$1604="o")*($B$23:$B$1604="c")*($E$23:$E$1604="Best
View-Current (SFU)")*(F$23:F$1604))

The final array, F$23:F$1604, is the one in question, where some of the
cells have text values. This is what I have tried so far...

Added dashes, and replaced '*' with ','
=SUMPRODUCT(--($A$23:$A$1604="o"),($B$23:$B$1604="c"),($E$23:$E$1604="Best
View-Current (SFU)"),(F$23:F$1604))
This got rid of the error, but now returns a 0, where the answer when I
delete the text cells is around 250,000.


Tried entering it as an array formula by using ctrl+shift+enter, still with
the dashes and ',' instead of '*'
{=SUMPRODUCT(--($A$23:$A$1604="o"),($B$23:$B$1604="c"),($E$23:$E$1604="Best
View-Current (SFU)"),IF(ISNUMBER(F$23:F$1604)=FALSE,0,F$23:F$1604))}
And the answer is again 0.

Tried entering it as an array formula without the dashes and with '*''s
instead of ',''s...
{=SUMPRODUCT(($A$23:$A$1604="o")*($B$23:$B$1604="c")*($E$23:$E$1604="Best
View-Current (SFU)")*IF(ISNUMBER(F$23:F$1604)=FALSE,0,F$23:F$1604))}

And the answer was correct, so, Rick, thanks for the tip.

Paul and Dave, I'm not sure exactly what the dashes are doing, nor why it
did not work in my case. Do they have a technical name I could use to google
more about it?

Thank you all, for taking time out of your day to consider my problem and
reply so promptly.

Greg

 

[Dave Peterson]

Try:

=SUMPRODUCT(--($A$23:$A$1604="o"),
--($B$23:$B$1604="c"),
--($E$23:$E$1604="Best View-Current (SFU)"),
(F$23:F$1604))

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

====================
A23:A1604="o"
will result in an array of 1582 true/falses. The -- stuff changes the true's to
1's and the false's to 0.

Put
TRUE
in A1

put
-a1
in B1

put
--a1
in C1

And you'll see why you want two of them.

 

[Greg Snidow]

Dave, that formula also works. I think I did not fully understand the use of
the dashes, so I only had them in front of the first array. I like this
method better, since it eliminates the use of an array formula, which could
easily be messed up by users not understanding that you can not just type in
the brackets. Thanks for the links, they are very informative.

 

[Dave Peterson]

Bob and J.E. did very nice work explaining how this works--and when you should
use the * operand, too!