Forum Discussion
Trouble with Countifs with Multiple criteria
I have a file that I want set so that when I dump my data into a tab it breaks everything down for me.
I'm have specific issues with multiple criteria. i.e the highlighted cells in the attached file. I can't get the formula to pull both ecom numbers and mpus numbers. I'm also have issues grabbing only the numbers for orders "Not allocated" and "waiting on plan" or vice versa all orders except "Not allocated" and "Waiting on plan"
Hopefully looking at the file it'll make more sense as to what i'm trying to do.
The formula in C30 should probably be
=COUNTIFS('OB Summary Report'!$O:$O,"Available- Not Allocated",'OB Summary Report'!$I:$I,A27,'OB Summary Report'!$J:$J,"MPUS")+
COUNTIFS('OB Summary Report'!$O:$O,"Available- Not Allocated",'OB Summary Report'!$I:$I,A27,'OB Summary Report'!$J:$J,"ECOM")+
COUNTIFS('OB Summary Report'!$O:$O,"Waiting for Load Plan",'OB Summary Report'!$I:$I,A27,'OB Summary Report'!$J:$J,"MPUS")+
COUNTIFS('OB Summary Report'!$O:$O,"Waiting for Load Plan",'OB Summary Report'!$I:$I,A27,'OB Summary Report'!$J:$J,"ECOM")and that in D30
=SUMIFS('OB Summary Report'!$X:$X,'OB Summary Report'!$O:$O,"Available- Not Allocated",'OB Summary Report'!$I:$I,A27,'OB Summary Report'!$J:$J,"MPUS")+
SUMIFS('OB Summary Report'!$X:$X,'OB Summary Report'!$O:$O,"Available- Not Allocated",'OB Summary Report'!$I:$I,A27,'OB Summary Report'!$J:$J,"ECOM")+
SUMIFS('OB Summary Report'!$X:$X,'OB Summary Report'!$O:$O,"Waiting for Load Plan",'OB Summary Report'!$I:$I,A27,'OB Summary Report'!$J:$J,"MPUS")+
SUMIFS('OB Summary Report'!$X:$X,'OB Summary Report'!$O:$O,"Waiting for Load Plan",'OB Summary Report'!$I:$I,A27,'OB Summary Report'!$J:$J,"ECOM")
8 Replies
You highlight cell C18 on the Summary sheet.
If I filter out the rows on the OB Summary Report sheet that have "Available- Not Allocated" or "Waiting for Load Plan" in column O, 3 rows remain:
So the value 3 in C18 appears to be correct.
What did you expect the result to be?
C30 should read 19 and D30 is coming up blank. I'm assuming its not returning ECOM part of the formula or "Waiting for Load Plan" but probably both.
Also if the formula for C30 & D30 is wrong I have to think C18 & D18 is also wrong.
You made mistake on date column reference for highlighted cell formula. For second countifs your formula is COUNTIFS('OB Summary Report'!$J:$J,"ECOM",'OB Summary Report'!$A:$A,"7000*",'OB Summary Report'!B:B,A3) Here A3 is a date value but 'OB Summary Report'!B:B is not your date column. So this would be I column 'OB Summary Report'!I:I. And full formula will be
=COUNTIFS('OB Summary Report'!$J:$J,"MPUS",'OB Summary Report'!$I:$I,A3)+COUNTIFS('OB Summary Report'!$J:$J,"ECOM",'OB Summary Report'!$A:$A,"7000*",'OB Summary Report'!$I:$I,A3)Thanks for pointing out the mistake there. That fixes that cell, but i'm still having issues with the other highlighted cells not grabbing all the data I need.
Noticed the same mistake in the other cells, fixed but still doesn't solve.
