# Multiple SUMIF conditions

#### elbastador

##### New Member
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!

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
You could also use the sumproduct formula as well.

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

Try:

Just add new conditions separated with + sign

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

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

Try,

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

Also no whole column references.

Or..

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

HTH

Krishnakumar said:
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 Akyurek said:
Krishnakumar said:
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.

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?

Replies
2
Views
93
Replies
4
Views
193
Replies
2
Views
100
Replies
3
Views
409
Replies
10
Views
421

Threads
1,207,439
Messages
6,078,573
Members
446,349
Latest member
Malroos7912

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back