COUNTIFS for multiple ranges

Debra N. Lopez

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

Paul Newcome

Nicely done. You mentioned something about "cleanliness"... I only have two suggestions for that.

First, you don't need all of the extra parenthesis around each section. Removing the unnecessary ones will help clean it up a touch.

Second, you can combine some of your text strings.

"PASS " + "[" + COUNTIFS()

vs

"PASS [" + COUNTIFS()

or

COUNTIFS() + "]" + " FAIL " + "[" + COUNTIF()

vs

COUNTIFS() + "] FAIL [" + COUNTIFS()

Here is what it would look like after those two things were "cleaned up":

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

Debra N. Lopez

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!!

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.

Debra N. Lopez

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

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

Paul Newcome

Nicely done. You mentioned something about "cleanliness"... I only have two suggestions for that.

First, you don't need all of the extra parenthesis around each section. Removing the unnecessary ones will help clean it up a touch.

Second, you can combine some of your text strings.

"PASS " + "[" + COUNTIFS()

vs

"PASS [" + COUNTIFS()

or

COUNTIFS() + "]" + " FAIL " + "[" + COUNTIF()

vs

COUNTIFS() + "] FAIL [" + COUNTIFS()

Here is what it would look like after those two things were "cleaned up":

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

Debra N. Lopez

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

Happy to help! 👍️

Those parenthesis can definitely be a bugger sometimes.