SUMIFS and multiple criteria in same column

thart21

Board Regular
Joined
Mar 3, 2005
Messages
159
I'm not getting any error messages but can't get this to sum column E with two criteria in column C. It will only sum for "Z001" and ignores "Z003".

=SUMIFS(Rejects!$E$2:$E$65,Rejects!$B$2:$B$65,$L$1,Rejects!$D$2:$D$65,$O$1,Rejects!$A$2:$A$65,I10,Rejects!$C$2:$C$65,{"Z001","Z003"})

Any ideas? Thanks!
 
Thanks for the quick response, but, unfortunately, it's still not working for me.

<TABLE style="WIDTH: 212pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=282 border=0><COLGROUP><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" width=145><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl138 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: silver" width=49 height=20>Month</TD><TD class=xl138 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 109pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=145>Order Notif - SO</TD><TD class=xl138 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 29pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=39>Plant</TD><TD class=xl138 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=49>SO Qty</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl139 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=49 height=20>Sep</TD><TD class=xl139 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; WIDTH: 109pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=145>IN PROCESS</TD><TD class=xl141 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; WIDTH: 29pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=39>1000</TD><TD class=xl140 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; WIDTH: 37pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=49>50</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl139 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=49 height=20>Sep</TD><TD class=xl139 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 109pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=145>IN PROCESS</TD><TD class=xl141 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 29pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=39>1100</TD><TD class=xl140 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 37pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=49>8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl139 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=49 height=20>Aug</TD><TD class=xl139 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 109pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=145>ON HOLD</TD><TD class=xl141 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 29pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=39>1000</TD><TD class=xl140 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 37pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=49>19</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl139 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=49 height=20>Jul</TD><TD class=xl139 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 109pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=145>ON HOLD</TD><TD class=xl141 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 29pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=39>1000</TD><TD class=xl140 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 37pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=49>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl139 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=49 height=20>Sep</TD><TD class=xl139 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 109pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=145>ON HOLD</TD><TD class=xl141 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 29pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=39>1000</TD><TD class=xl140 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 37pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=49>6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl139 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=49 height=20>Aug</TD><TD class=xl139 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 109pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=145>ON HOLD</TD><TD class=xl141 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 29pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=39>1100</TD><TD class=xl140 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 37pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=49>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl139 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=49 height=20>Jul</TD><TD class=xl139 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 109pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=145>ON HOLD</TD><TD class=xl141 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 29pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=39>1100</TD><TD class=xl140 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 37pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=49>8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl139 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=49 height=20>Sep</TD><TD class=xl139 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 109pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=145>ON HOLD</TD><TD class=xl141 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 29pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=39>2100</TD><TD class=xl140 style="BORDER-RIGHT: #d0d7e5 0.5pt solid; BORDER-TOP: #d0d7e5; BORDER-LEFT: #d0d7e5; WIDTH: 37pt; BORDER-BOTTOM: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent" width=49>1</TD></TR></TBODY></TABLE>

This gives me 29 which is correct for 1000:
=SUM(SUMIFS($D$3:$D$31,$B$3:$B$31,"ON HOLD",$C$3:$C$31,1000,$A$3:$A$31,{"Jul","Aug","Sep"}))

This gives me 4 which is coming from the 4th row of data (I checked by changing the #):
=SUM(SUMIFS($D$3:$D$31,$B$3:$B$31,"ON HOLD",$C$3:$C$31,{1000,2100},$A$3:$A$31,{"Jul","Aug","Sep"}))

What am I missing?
You have too many "ors" for that set of functions.

Try it like this...

=SUMPRODUCT(--(ISNUMBER(MATCH(A3:A10,{"Jul","Aug","Sep"},0))),--(B3:B10="On Hold"),--(ISNUMBER(MATCH(C3:C10,{1000,2100},0))),D3:D10)

My personal preference would be to use cells to hold all the criteria.

X1:X3 = Jul, Aug, Sep
Y1 = On Hold
Z1:Z2 = 1000, 2100

=SUMPRODUCT(--(ISNUMBER(MATCH(A3:A10,X1:X3,0))),--(B3:B10=Y1),--(ISNUMBER(MATCH(C3:C10,Z1:Z2,0))),D3:D10)
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Hi,
Thank you and this is a very nice tip. I am trying to use this in my VBA macro and it shows "Invalid Character" Error for { symbol. Is there a way I can use the method suggested here in my macro. It will really reduce lot of lines of code since I repeate many SumIFs lines in my code.

regards
ramkisuni
 
Upvote 0
Hi,
Sorry to be more clear on my previous query, I am refering to the line "=SUM(SUMIFS(Rejects!$E$2:$E$65,Rejects!$B$2:$B$65,$L$1,Rejects!$D$2:$D$65,$O$1,Rejects!$A$2:$A$65,I10,Rejects!$C$2:$C$65 ,{"Z001","Z003"}))"

I converted this to my macro like

DevStrtcolRng.Offset(row, col - 1).Value = _
Application.SumIfs(costEffctvHrs, costRefRng, .Range("A" & row + 1), costPeriod, monthRng(1, col), costLocationRng, "LocName", costWorkTypeRng, {"Development","Requirements/FSD Definition", "Scoping"})

and I get Invalid Character Errort for {

Can you please help me to understand what is the problem here?

Thanks
 
Upvote 0
Hi,
Sorry to be more clear on my previous query, I am refering to the line "=SUM(SUMIFS(Rejects!$E$2:$E$65,Rejects!$B$2:$B$65,$L$1,Rejects!$D$2:$D$65,$O$1,Rejects!$A$2:$A$65,I10,Rejects!$C$2:$C$65 ,{"Z001","Z003"}))"

I converted this to my macro like

DevStrtcolRng.Offset(row, col - 1).Value = _
Application.SumIfs(costEffctvHrs, costRefRng, .Range("A" & row + 1), costPeriod, monthRng(1, col), costLocationRng, "LocName", costWorkTypeRng, {"Development","Requirements/FSD Definition", "Scoping"})

and I get Invalid Character Errort for {

Can you please help me to understand what is the problem here?

Thanks
I'm not much of a programmer so someone else will need to help you with this.

I suggest that you start a new thread and make sure you note in the thread subject that you need VBA help.
 
Upvote 0
DevStrtcolRng.Offset(row, col - 1).Value = _
Application.SumIfs(costEffctvHrs, costRefRng, .Range("A" & row + 1), costPeriod, monthRng(1, col), costLocationRng, "LocName", costWorkTypeRng, {"Development","Requirements/FSD Definition", "Scoping"})

and I get Invalid Character Errort for {

Can you please help me to understand what is the problem here?

Thanks

You need to change formula to display the below
DevStrtcolRng.Offset(row, col - 1).Value = _
Application.SumIfs(costEffctvHrs, costRefRng, .Range("A" & row + 1), costPeriod, monthRng(1, col), costLocationRng, "LocName", costWorkTypeRng,ARRAY("Development","Requirements/FSD Definition", "Scoping"))

if you used .FormulaR1C1 instead of .Value it should work (note its would write the formula into the cell, but based on what you are looking use the other way as only the output will be displayed within the cell)
 
Last edited:
Upvote 0
Hi Kevin,
Excellent, Thank you very much. It solved the problem but with a small correction (which I missed in my formula entry)

The corrected version is
DevStrtcolRng.Offset(row, col - 1).Value = _
Application.sum (Application.SumIfs(costEffctvHrs, costRefRng, .Range("A" & row + 1), costPeriod, monthRng(1, col), costLocationRng, "LocName", costWorkTypeRng,ARRAY("Development","Requirements/FSD Definition", "Scoping")))

without Application.Sum, it considers only the first element in the array.

With warm regards
ramkisuni
 
Upvote 0
Hi,

Could somebody help me converting this sumifs criteria from "equals" to "does not equal"?
{"SH0S204","SH0B201","SH0D220","SH0D201","SH0D202"}

Thanks in advance!

Kind regards,
Paul
 
Upvote 0
=SUM(SUMIFS($D$3:$D$31,$B$3:$B$31,"ON HOLD",$C$3:$C$31,{1000,2100},$A$3:$A$31,{"Jul";"Aug";"Sep"}))

Colons have to be used for second array argument, no idea why.
Can anyone help?
 
Upvote 0

Forum statistics

Threads
1,224,012
Messages
6,175,931
Members
452,686
Latest member
midhunjoseph

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

Which adblocker are you using?

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
Back
Top