Complex SUMIF

[Guest]

First...this site is great. Such positive feedback responses and quick too.

I am looking for formula help.

I have a list of "Names" in Cells A1:A15 (e.g. A1=John, A2=Jane, A3=John,
A4=Fred, A5=John…).

In Column B, I have a list of "Last Names" in Cells B1:B15 (e.g. B1=Smith,
B2=Jones, B3=Edwards, B4=Smith, B5=Smith...).

In Column C, I have a list of numbers in Cells C1:C15 (e.g.
C1=1,C2=2,C3=3,C4=4,C5=5,...).

I would like to set up a formula in Column D where I indicate that if the
criteria in Column A is "John" and Column B criteria is "Smith" then sum the
numbers in Column C. So in my example, there are two instances of "John
Smith." The numbers should total 6 (1+5). Is there a formula for this? For
some reason I cannot get a SUMIF formula to work. To further complicate
matters, I need the formula to ignore blanks.

Help...please!

 

[PCLIVE]

Try this:

=SUMPRODUCT(--(A1:A15="John"),--(B1:B15="Smith"),C1:C15)

HTH,
Paul

 

[Don Guillett]

=sumproduct((a1:a15="John")*(b1:b15="Smith")*c1:c15)
or instead of john use b1 if John is in b1. Same for smith

 

[Roger Govier]

Hi

Try
=SUMPRODUCT(--($A1:$A$100="John"),--($B$1:$B$100="Smith"),$C$1:$C$100)

You could put the John and Smith in separate cells e.g. D1 and EI and use
=SUMPRODUCT(--($A1:$A$100=$D$1),--($B$1:$B$100=$E$1),$C$1:$C$100)
then just changing the values in D1 and E1 will give you results for other
people.

 

[Guest]

Thanks everyone!!! These all worked. I appreciate the quick responses