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.

--
Biff
Microsoft Excel MVP


"my" wrote in message
...
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!