Countifs Question

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 Khalil
    Bassam Khalil ✭✭✭✭✭✭
    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

    bassam.khalil2009@gmail.com

    ☑️ 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 Brady
    Olivia Brady ✭✭✭
    Answer ✓

    Many thanks that worked :)

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    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

    bassam.khalil2009@gmail.com

    ☑️ 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 Brady
    Olivia Brady ✭✭✭
    Answer ✓

    Many thanks that worked :)

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

    @Olivia Brady 

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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • 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 Khalil
    Bassam 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 : Bassam.k@mobilproject.it

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

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




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!