Sumproduct - multiple criteria in Column A

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?

Hi

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

Cheers
JulieD

"briank" <[email protected]> wrote in message
news:[email protected]...
>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?

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)


"briank" <[email protected]> wrote in message
news:[email protected]...
> 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?