Sumproduct Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

A B
1 cat 100
2 horse 200
3 fish 100
4 Cat 50
5 Dog 75
6 horse 100
7 fish 300
8
9 =SUMPRODUCT((a1:a7="cat")*(a1:a7="fish")*B1:B7)

Should equal 550 but my formula is returning 0. The formula is copy/paste from spreadsheet. What am I doing wrong?
Many thanks!!!!
 
Hi Sandy

Your formula:
=SUMPRODUCT((a1:a7="cat")*(a1:a7="fish")*B1:B7)

It's asking to sum where a single entry in A is cat AND fish. Since
that doesn't happen the answer is 0.

Try:
=SUMPRODUCT((A1:A7="cat")*(B1:B7))+SUMPRODUCT((A1:A7="fish")*(B1:B7))
Returns 550

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Sandy,

As an alternative to Normans fine reply:

=SUMPRODUCT((A1:A7={"CAT","FISH"})*B1:B7)

Biff
-----Original Message-----
A B
1 cat 100
2 horse 200
3 fish 100
4 Cat 50
5 Dog 75
6 horse 100
7 fish 300
8
9 =SUMPRODUCT((a1:a7="cat")*(a1:a7="fish")*B1:B7)

Should equal 550 but my formula is returning 0. The
Click to expand...
formula is copy/paste from spreadsheet. What am I doing
wrong?
 
There is more than one way to skin a cat (pun intended). In addition to
the two ways alrady mentioned, you could use

=SUMPRODUCT((A1:A7="cat")+(A1:A7="fish"),B1:B7)

("*" corresponds to "AND" and "+" corresponds to "OR") or

=SUMIF(A1:A7,"cat",B1:B7)+SUMIF(A1:A7,"fish",B1:B7)

Jerry
 
Back
Top