Forum Discussion

Rodney2485's avatar
Rodney2485
Brass Contributor
Jan 11, 2025
Solved

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 ...
excel
Formulas and Functions
office 365
  • HansVogelaar's avatar
    HansVogelaar
    Jan 13, 2025

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

Harun24HR's avatar
Harun24HR
Silver Contributor
Jan 11, 2025

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)

 

Rodney2485's avatar
Rodney2485
Brass Contributor
Jan 13, 2025

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.