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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

Greg Snidow wrote:

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