Sumif where two conditions are met

  • Thread starter Thread starter coburg3058
  • Start date Start date
C

coburg3058

I wish to sum the contents of a column based on the two columns meeting
certain criteria.
A B C D E

1 Male Peter Red 25 Male-Red
2 Male Paul Black 10 Male-Black
3 Female Mary Black 20 Female-Black
4 Male John Red 10 Male-Red

The only way I've come up with so far is to create another column which
is a contactonation of the two columns and then use a
=SUMIF(E1 : E4, "Male-Red", D1 : D4)

Would like to be able to have something like
=sum(productof(?)(A1 : A4 & C1 : C4), "Male-Red", D1 : D4)

Preferably without using VBA.
Thanks
Simon
 
One way:

=SUM((A1:A4="Male")*(C1:C4="Red")*(D1:D4))
(Array-entered -- Hit Ctrl-Shift-Enter instead of just Enter)

Another way:

=SUMPRODUCT(--(A1:A4="Male"),--(C1:C4="Red"),D1:D4)

/i.
 
Back
Top