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

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.

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!