Text in column causing SUMPRODUCT error
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
--
Rick (MVP - Excel)
"Greg Snidow" wrote in message
...
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.
|