Sumproduct - multiple criteria in Column A

[briank]

I am trying to get the sumproduct to look for two particular names in Column
A and based upon both of these names add up numbers in Column N. This is not
working as I cannot get sumproduct to add up these numbers.

i.e. =SUMPRODUCT(--('$A$5:$A$54="Smith"),--($A$5:$A$54="Miller"),!N$5:N$54)

I'm thinking that looking for the names Smith AND Miller isn't working but
rather looking for the names Smith OR Miller is what I want. Is there a way
to make this work?

 

[JulieD]

Hi

how about
=SUMIF(A5:A54,"Smith",N5:N54)+SUMIF(A5:A54,"Miller",N5:N54)

Cheers
JulieD

 

[Dave R.]

Right - A5 for example won't (in this case, probably) contain "Smith" and
"Miller", but one or the other.

For Smith or Miller, try;

=SUMPRODUCT(($A$5:$A$54="Smith")+($A$5:$A$54="Miller"),N$5:N$54)