SUMIF with multiple conditions

V

vtisix

I am looking for a way to shorten multiple SUMIF formulas, i.e. column A contains reference #s, column B contains values. Currently if I am looking to sum multiple values in column A, I use the following:

=SUMIF(A:A,"=Black",B:B) + SUMIF(A:A,"=Yellow",B:B) + SUMIF().....

As you can see, this can get really cumbersome to work with if I am looking for 4 or more conditions. I am looking for a way to do the following (in principle):

=SUMIF(A:A,"Condition 1"; "Condition 2"; "Condition 3"....,B:B)

The formula basically goes into column A, finds all the conditions and sums up corresponding values in column B.

I have dabbled with the SUMPRODUCT fomula, ie.

=SUMPRODUCT(--(A1:A60000="Black"),--(A1:A60000="Yellow"),B1:B60000) - this returns #NUM error.

Any thoughts? This has been driving me bonkers for the last couple of days. Any help is much appreciated.

Thanks,

VT
 
V

Vasant Nanavati

Try:

=SUM(OR((A1:A60000="Black"),(A1:A60000="Yellow"))*(B1:B60000))

entered as an array formula with <Ctrl> <Shift> <Enter>.

Unfortunately, array formulas do not work well with entire-column ranges
such as A:A.

--

Vasant



vtisix said:
I am looking for a way to shorten multiple SUMIF formulas, i.e. column A
contains reference #s, column B contains values. Currently if I am looking
to sum multiple values in column A, I use the following:
=SUMIF(A:A,"=Black",B:B) + SUMIF(A:A,"=Yellow",B:B) + SUMIF().....

As you can see, this can get really cumbersome to work with if I am
looking for 4 or more conditions. I am looking for a way to do the
following (in principle):
=SUMIF(A:A,"Condition 1"; "Condition 2"; "Condition 3"....,B:B)

The formula basically goes into column A, finds all the conditions and
sums up corresponding values in column B.
I have dabbled with the SUMPRODUCT fomula, ie.

=SUMPRODUCT(--(A1:A60000="Black"),--(A1:A60000="Yellow"),B1:B60000) - this returns #NUM error.

Any thoughts? This has been driving me bonkers for the last couple of
days. Any help is much appreciated.
 
B

Biff

Here's another method of using SUMPRODUCT:

=SUMPRODUCT((A1:A60000={"BLACK","YELLOW"})*(B1:B60000))

Biff
-----Original Message-----
I am looking for a way to shorten multiple SUMIF
formulas, i.e. column A contains reference #s, column B
contains values. Currently if I am looking to sum
multiple values in column A, I use the following:
=SUMIF(A:A,"=Black",B:B) + SUMIF(A:A,"=Yellow",B:B) + SUMIF().....

As you can see, this can get really cumbersome to work
with if I am looking for 4 or more conditions. I am
looking for a way to do the following (in principle):
=SUMIF(A:A,"Condition 1"; "Condition 2"; "Condition 3"....,B:B)

The formula basically goes into column A, finds all the
conditions and sums up corresponding values in column B.
I have dabbled with the SUMPRODUCT fomula, ie.

=SUMPRODUCT(--(A1:A60000="Black"),--
(A1:A60000="Yellow"),B1:B60000) - this returns #NUM error.
Any thoughts? This has been driving me bonkers for the
last couple of days. Any help is much appreciated.
 
A

Aladin Akyurek

=SUM(SUMIF(A:A,{"Black","Yellow","Pink","Green"},B:B))

=SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$100,{"Black","Yellow","Pink","Green"},0
)),$B$2:$B$100)

=SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$100,X2:X5,0)),$B$2:$B$100)

where X2:X5 houses conditions that you want to OR.

Note that formulas which operate on computed arrays do not admit whole
columns (e.g., A:A) as reference.

vtisix said:
I am looking for a way to shorten multiple SUMIF formulas, i.e. column A
contains reference #s, column B contains values. Currently if I am looking
to sum multiple values in column A, I use the following:
=SUMIF(A:A,"=Black",B:B) + SUMIF(A:A,"=Yellow",B:B) + SUMIF().....

As you can see, this can get really cumbersome to work with if I am
looking for 4 or more conditions. I am looking for a way to do the
following (in principle):
=SUMIF(A:A,"Condition 1"; "Condition 2"; "Condition 3"....,B:B)

The formula basically goes into column A, finds all the conditions and
sums up corresponding values in column B.
I have dabbled with the SUMPRODUCT fomula, ie.

=SUMPRODUCT(--(A1:A60000="Black"),--(A1:A60000="Yellow"),B1:B60000) - this returns #NUM error.

Any thoughts? This has been driving me bonkers for the last couple of
days. Any help is much appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top