Countifs Question

10/14/21
Accepted

HI I am trying to do a Countifs using the below, the result keeps coming back as 0 but I think that is because the box only has either Pass or Fail in it but not both in the same cell, I am trying to add up all Pass and Fail Inspections, how do I include both Pass and Fail results.

=COUNTIFS([Building Unit]:[Building Unit], "Stamullen U6", [First Aid Box Adequately Stocked]:[First Aid Box Adequately Stocked], "Pass", [First Aid Box Adequately Stocked]:[First Aid Box Adequately Stocked], "Fail", Month:Month, "1")


thanks

Best Answers

  • Bassam KhalilBassam Khalil ✭✭✭✭✭
    Accepted Answer

    Hi @Olivia Brady

    Hope you are fine, please try the following formula:

    =COUNTIFS([Building Unit]:[Building Unit], @cell = "Stamullen U6", Month:Month, @cell = 1,
    [First Aid Box Adequately Stocked]:[First Aid Box Adequately Stocked], OR(@cell = "Pass",
     @cell = "Fail"))
    

    the following screenshot shows the result:


    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Olivia BradyOlivia Brady ✭✭✭✭✭
    Accepted Answer

    Many thanks that worked :)

Answers

  • Bassam KhalilBassam Khalil ✭✭✭✭✭
    Accepted Answer

    Hi @Olivia Brady

    Hope you are fine, please try the following formula:

    =COUNTIFS([Building Unit]:[Building Unit], @cell = "Stamullen U6", Month:Month, @cell = 1,
    [First Aid Box Adequately Stocked]:[First Aid Box Adequately Stocked], OR(@cell = "Pass",
     @cell = "Fail"))
    

    the following screenshot shows the result:


    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Olivia BradyOlivia Brady ✭✭✭✭✭
    Accepted Answer

    Many thanks that worked :)

  • Bassam KhalilBassam Khalil ✭✭✭✭✭
    edited 10/14/21

    @Olivia Brady 

    Excellent, i will be happy to help you any time.

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Olivia BradyOlivia Brady ✭✭✭✭✭

    Many thanks, sorry just one more thing on this formula, if I wanted to do this lookup but put the results on a different smartsheet for Countifs referencing this smartsheet how would the formula look

    thanks again

  • Bassam KhalilBassam Khalil ✭✭✭✭✭

    @Olivia Brady 

    if you like me to fix the formula directly on your sheet please share me as an admin on a sample copy of your sheets ( Source & Destination ) and i will Create the exact formula for you then you can copy it to your original sheet.


    My Email for sharing : [email protected]

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Olivia BradyOlivia Brady ✭✭✭✭✭

    many thanks I was trying to share the sheets with you, but getting the below message




Sign In or Register to comment.