Multiple Criteria (Excel: SUMIF vs. SUMPRODUCT)

  • Thread starter Thread starter Cecilia
  • Start date Start date
C

Cecilia

Hello,
I need help with multiple criterias in Excel. From earlier messages in
this group I have understood that I should use SUMPRODUCT but I can't
get it to work properly. Excel just gives me the output #VALUE!. (I
use Excel 2000.)

My problem is the following:

Month Person USD
Apr 1 20
Apr 2 30
May 1 20
May 2 20

=SUMPRODUCT((A1:A5="Apr")*(B1:B5=1)*C1:C5)

I would like to sum the USD when the month is April and the person is
number 1.

Do I need to use Control+Shift+Enter or can I just use Enter to
confirm the formula? (Unfortunately nothing of this works in the
example above.)

Bye,
Cecilia :)
 
I think the issue is that you're including the headers in
your ranges (row 1), and you end up trying to multiply a
FALSE with a text string (USD) which results in an error.
Change all your ranges to start with row 2 (like A2:A5).

HTH
Jason
Atlanta, GA
 
Change the range to exclude the headers, ie

=SUMPRODUCT((A2:A5="Apr")*(B2:B5=1)*C2:C5)

Other than that, what you have should work, AS LONG as the Apr May etc that we
are seeing in the range A2:A5 are really that, and are not dates fields
formatted as mmm.

Side-note, you have omitted the last set of parentheses from the C2:C5 which is
absolutely correct, BUT, leaving them in can make it easier for somebody else to
follow the thread of the formula. Won't affect anything, but can make it easier
to read ;-)

=SUMPRODUCT((A2:A5="Apr")*(B2:B5=1)*(C2:C5))

Also, if you intended to copy that formula down or across at all, make sure you
lock the references, otherwise they will all change, eg:-

=SUMPRODUCT(($A$2:$A$5="Apr")*($B$2:$B$5=1)*($C$2:$C$5))

Just following on from that logic, you may want to list the Months in a range,
eg H1:H12 and then in I1 put the formula, but refer to the value in cell H1 for
your month argument, eg:-

=SUMPRODUCT(($A$2:$A$5=$H1)*($B$2:$B$5=1)*($C$2:$C$5))

You could now copy this down to I12 and it would pick up the month from the cell
to the left of the formula each time. Note that i only locked the H part of the
reference, as otherwise the row reference wouldn't increment each time. Copying
down you will see this:-

=SUMPRODUCT(($A$2:$A$5=$H1)*($B$2:$B$5=1)*($C$2:$C$5))
=SUMPRODUCT(($A$2:$A$5=$H2)*($B$2:$B$5=1)*($C$2:$C$5))
=SUMPRODUCT(($A$2:$A$5=$H3)*($B$2:$B$5=1)*($C$2:$C$5))
=SUMPRODUCT(($A$2:$A$5=$H4)*($B$2:$B$5=1)*($C$2:$C$5))
=SUMPRODUCT(($A$2:$A$5=$H5)*($B$2:$B$5=1)*($C$2:$C$5))
etc............
 
...
...
My problem is the following:

Month Person USD
Apr 1 20
Apr 2 30
May 1 20
May 2 20

=SUMPRODUCT((A1:A5="Apr")*(B1:B5=1)*C1:C5)
Click to expand...
...

Problem is C1 is text. That's a problem for the * operator, but not for
SUMPRODUCT. As an alternative to the other solutions provided already, try

=SUMPRODUCT((A1:A5="Apr")*(B1:B5=1),C1:C5)

[This solution, obvious in hindsight (ain't that always the case), was given by
Dana DeLouis a few weeks ago.]
 
Back
Top