Hi,

I'm trying to use sumproduct with multiple criteria but in a single column.
The data looks like this:

A B
Salary 500
Bonus 400
Fringe 300
Travel 100
Entertainment 100

I want to sum column B that fits the criteria of "Salary", "Bonus", "Fringe"
in column A.

Is there a way to do an array formula or something where the formula checks
through column A and sums all values that meet the criteria of "Salary",
"Bonus", "Fringe"?

Thanks!

=SUMPRODUCT(--(A1:A5={"Salary","Bonus","Fringe"})*(B1:B5))

"my" wrote:

Hi,

I'm trying to use sumproduct with multiple criteria but in a single column.
The data looks like this:

A B
Salary 500
Bonus 400
Fringe 300
Travel 100
Entertainment 100

I want to sum column B that fits the criteria of "Salary", "Bonus", "Fringe"
in column A.

Is there a way to do an array formula or something where the formula checks
through column A and sums all values that meet the criteria of "Salary",
"Bonus", "Fringe"?

Thanks!

What if I wanted to have those criteria text in column C instead?
A B C
Salary 500 Salary
Bonus 400 Bonus
Fringe 300 Fringe
Travel 100
Entertainment 100

I tried doing =SUMPRODUCT(--(A1:A5={C1,C2,C3})*(B1:B5))
but Excel won't let me click on C1 with those curly brackets...

"Toppers" wrote:

=SUMPRODUCT(--(A1:A5={"Salary","Bonus","Fringe"})*(B1:B5))

"my" wrote:

Hi,

I'm trying to use sumproduct with multiple criteria but in a single column.
The data looks like this:

A B
Salary 500
Bonus 400
Fringe 300
Travel 100
Entertainment 100

I want to sum column B that fits the criteria of "Salary", "Bonus", "Fringe"
in column A.

Is there a way to do an array formula or something where the formula checks
through column A and sums all values that meet the criteria of "Salary",
"Bonus", "Fringe"?

Thanks!

One way:

=SUMPRODUCT(((A1:A1000="Salary") + (A1:A1000="Bonus") +
(A1:A1000="Fringe")), B1:B1000)

In article ,
my wrote:

Hi,

I'm trying to use sumproduct with multiple criteria but in a single column.
The data looks like this:

A B
Salary 500
Bonus 400
Fringe 300
Travel 100
Entertainment 100

I want to sum column B that fits the criteria of "Salary", "Bonus", "Fringe"
in column A.

Is there a way to do an array formula or something where the formula checks
through column A and sums all values that meet the criteria of "Salary",
"Bonus", "Fringe"?

Thanks!

See reply from Mr McGimpsey re this question and please do not start a new
thread on the same subject.......

=SUMPRODUCT(((A1:A1000=C1) + (A1:A1000=C2) + (A1:A1000=C3)), B1:B1000)


"my" wrote:

Hi,

I'm trying to use sumproduct with multiple criteria but in a single column.
The data looks like this:

A B
Salary 500
Bonus 400
Fringe 300
Travel 100
Entertainment 100

I want to sum column B that fits the criteria of "Salary", "Bonus", "Fringe"
in column A.

Is there a way to do an array formula or something where the formula checks
through column A and sums all values that meet the criteria of "Salary",
"Bonus", "Fringe"?

Thanks!