SUMIF with multiple conditions

[Guest]

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

 

[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

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.

 

[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.

 

[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.

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.