SUMIFS and multiple criteria in same column

[thart21]

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!

 

[F&s anaylst]

Hi there,

check for blank spaces in data range or add trim function (see below) into your sum range (remember it's an array so Ctrl+Shift+Enter)

{=SUMIFS(trim(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"})
}

 

[thart21]

Thanks for the reply! No blanks in my data range, so I should do an array for both the criteria AND the entire formula?
Just tried it and it still isn't picking up the Z003. I'll play around with it some more but don't know why it wouldn't work.

Thanks again and I would appreciate any more ideas you might have for me!

Toni

 

[F&s anaylst]

Sorry Toni,

i put the trim around the sum range instead of the criteria range, try the below

{=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,trim(Rejects!$C$2:$C$65),{"Z001","Z003"})
}

is your data imported from any other system it could have hidden chars in range?

 

[T. Valko]

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!

Try it like this...

=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"}))

 

[thart21]

You are both awesome! Thanks so much, it works and I even understand WHY it works!

What a cool function!

Toni

 

[T. Valko]

You are both awesome! Thanks so much, it works and I even understand WHY it works!

What a cool function!

Toni

You're welcome. Thanks for the feedback!

 

[ops analyst]

Hi,
I found this post through Google & it helped me tremendously. I would like to know if you can have 2 columns with multiple criteria. Or have the criteria for one of the columns not equal a certain value. I've tried numerous things & none are working.

Currently have:
=SUM(SUMIFS('In Process-On Hold'!$E$3:$E$31,'In Process-On Hold'!$B$3:$B$31,"ON HOLD",'In Process-On Hold'!$D$3:$D$31,"1000",'In Process-On Hold'!$A$3:$A$31,{"Jul","Aug","Sep"}))

For the underlined, the choices are 1000, 1100 & 2100. I would like it to include 1000 & 2100. I've tried doing the brackets {"1000","2100"} & I've tried "<>1100", but neither end up pulling the correct number.

I would appreciate any help. Thanks!

 

[F&s anaylst]

Currently have:
=SUM(SUMIFS('In Process-On Hold'!$E$3:$E$31,'In Process-On Hold'!$B$3:$B$31,"ON HOLD",'In Process-On Hold'!$D$3:$D$31,"1000",'In Process-On Hold'!$A$3:$A$31,{"Jul","Aug","Sep"}))
!

Hi there,

welcome to the board reason why its not working it because you are telling excel to look for text value (because you have number wrapped in ""), you just need to remove then and it will work fine for you like before

=SUM(SUMIFS('In Process-On Hold'!$E$3:$E$31,'In Process-On Hold'!$B$3:$B$31,"ON HOLD",'In Process-On Hold'!$D$3:$D$31,{1000,2000},'In Process-On Hold'!$A$3:$A$31,{"Jul","Aug","Sep"}))

 

[ops analyst]

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

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