Multiple SUMIF conditions

[elbastador]

Suppose I have some data with a code for each data point:
1 100
1 200
1 300
2 400
2 500
3 600

The first column is the code and the second is the data. I can use a SUMIF statement to sum all the data that have a certain code (like 1). What if I wanted to sum all data that meets one of a number of codes? Suppose I wanted to sum all data that meets 1 or 2. I know I can do this with 2 separate SUMIFs, but I was wondering if there was a way to do it with one.

Thanks!

 

[andyh69uk]

This should help:-

http://office.microsoft.com/en-us/assistance/HA011266271033.aspx

Andy

 

[Legacy 51064]

You could also use the sumproduct formula as well.

=SUMPRODUCT((A:A=1)*(B:B=100)*(B:B))

 

[NBVC]

Try:

Just add new conditions separated with + sign

=SUMPRODUCT((a1:a10=1)+(a1:a10=2),b1:b10)

 

[Drew]

Sumproduct is the way to go... but to answer your sumif question, yes you can have multiple criteria...

use
=SUM(SUMIF(A1:A6,{1,2},B1:B6))
or if you need to reference your critieria
=SUM(INDEX(SUMIF(A1:A6,E2:F2,B1:B6),0,0))
where E2 houses 1 and F2 houses 2

 

[Brian from Maui]

Try,

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,{1,2},0))),B1:B10)

Also no whole column references.

 

[Krishnakumar]

Or..

=SUMPRODUCT(--(A1:A6={1,2})*(B1:B6))

HTH

 

[Aladin Akyurek]

Or..

=SUMPRODUCT(--(A1:A6={1,2})*(B1:B6))

HTH

Why the double negation for * is already there and required by the presence of the ={1,2} bit?

BTW, an idiom like ={1,2,...} leads to too many evaluations. That's why SUM/SUMPRODUCT with SUMIF or SUMPRODUCT with ISNUMBER/MATCH are better idioms.

 

[Krishnakumar]

Or..

=SUMPRODUCT(--(A1:A6={1,2})*(B1:B6))

HTH

Why the double negation for * is already there and required by the presence of the ={1,2} bit?

BTW, an idiom like ={1,2,...} leads to too many evaluations. That's why SUM/SUMPRODUCT with SUMIF or SUMPRODUCT with ISNUMBER/MATCH are better idioms.

Aladin,

Thanks for the info.

 

[mansoormanzoor]

If & Sumif

Hi, I have a data range in the coloumns below.(e.g...

ColA ColB ColC
A-412 11 100.00
A-413 11 200.00
A-412 21 300.00
A-413 21 400.00
......
I have to sort on Col B and then use the following SUMIFs:

SUMIF(A1:A2,D1,C1:C2) for 11 where D1 is A-412
SUMIF(A3:A4,D2,C3:C4) for 21 where D1 is A-412
....
and so on i use separate SUMIFS for different changes in col B.

How can I avoid multiple SUMIFs?