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

Rodney2485's avatar
Rodney2485
Brass Contributor
Jan 13, 2025

Also if the formula for C30 & D30 is wrong I have to think C18 & D18 is also wrong.

HansVogelaar's avatar
HansVogelaar
MVP
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")

  • Rodney2485's avatar
    Rodney2485
    Brass Contributor
    Jan 13, 2025

    This has worked and I was able to apply to remaining cells to fix additional errors. Thank you!