COUNTIFS for multiple ranges

I am trying to use COUNTIFS to add the values in multiple ranges for items that have a value of PASS. So far I can only pull the value in 1 range and i get "unparseable" if I try to add the values together---any ideas?

=("PASS " + "[" + (COUNTIFS(Status12:Status16, "PASS")) + "]")---this works only for 1 range of values

Best Answers

  • Debra N. Lopez
    Answer ✓

    Hi Paul,

    Thanks for the tip!!! I had a really hard time figuring where to pull out the 'extra' parenthesis to avoid the dreaded 'unparseable' error.

    Thank you!!

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Can you share a screenshot of the data (without exposing any confidential information)? It might be helpful to see what you're talking about here.

  • Hey Mike,

    I think I just answered my own question. I am trying to count the status of test cases (PASS, FAIL, INCOMPLETE). I was using COUNTIF instead of COUNTIFS.

    This may not have been the cleanest way, but it seemed to do the trick by ignoring blank values in between multiple ranges:

    =("PASS " + "[" + COUNTIFS(Status12:Status23, "PASS") + "]") + (" FAIL " + "[" + COUNTIF(Status12:Status23, "FAIL") + "]") + (" INCOMPLETE " + "[" + COUNTIF(Status12:Status23, "NOT STARTED") + "]")


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Nice, glad you got that working for you! :)

  • Debra N. Lopez
    Answer ✓

    Hi Paul,

    Thanks for the tip!!! I had a really hard time figuring where to pull out the 'extra' parenthesis to avoid the dreaded 'unparseable' error.

    Thank you!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️


    Those parenthesis can definitely be a bugger sometimes.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!