sumproduct with multiple criteria in single column

[Guest]

What if I wanted to have the criteria text in column C and wanted to sum the
values in Column B that matches the citeria specified in Column C in Column
A?
A B C
Salary 500 Salary
Bonus 400 Bonus
Fringe 300 Fringe
Travel 100
Entertainment 100

Someone suggested using the following, if I were specifying the criteria in
quotes. And this works.
=SUMPRODUCT(--(A1:A5={"Salary","Bonus","Fringe"})*(B1:B5))

But I want to reference Column C for the criteria. I tried doing
=SUMPRODUCT(--(A1:A5={C1,C2,C3})*(B1:B5))
but Excel returns an error message if I try this formula. Any help would be
much appreciated.

Thank you!

 

[T. Valko]

Try it like this:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,C1:C3,0))),B1:B5)

=SUMPRODUCT(--(A1:A5={C1,C2,C3})*(B1:B5))

The {.....} is called an array constant. You can't use cells references in
an array constant.

 

[Roger Govier]

Hi

In D1 enter
=SUMPRODUCT(--($A$1:$A$5=C1)*($B$1:$B$5))
copy down for as many entries as you have in column C.

 

[Guest]

It worked like a charm! Thank you!

If you have a moment, can you explain what this formula is doing exactly?

Thanks again,

 

[Bob Phillips]

MATCH(A1:A5,C1:C3,0) looks up the 3 lookup values against the data table and
returns an array of row indexes and no matches

ISNUMBER is used get an array of TRUE/FALSE for those matches/mis-matches

The -- coerces that array of TRUE/FALSE to 1/0

The array of 1/0 is PRODUCT'ed with their equivalents in B1:B5 to give an
array of 500,400,300,0,0

And SUM sums it

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)