sumproduct with multiple criteria in single column
 

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!

sumproduct with multiple criteria in single column
 

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!

sumproduct with multiple criteria in single column
 

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.

--
Regards

Roger Govier


"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!

sumproduct with multiple criteria in single column
 

It worked like a charm! Thank you!

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

Thanks again,

"T. Valko" wrote:

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!

sumproduct with multiple criteria in single column
 

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)

"my" wrote in message
...
It worked like a charm! Thank you!

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

Thanks again,

"T. Valko" wrote:

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!