Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Count of multiple values
Hi
I'm workng on a risk log template and want to summarise the number of red risks (score 20), amber risks (score 1519), number of yellow risks (score 1014) and the number of gree risks (score <10).
I thought I could use a COUNTIFS statement but I can only get it working to count all of just ONE value (example: 12).
=COUNTIF(Score:Score, 12)
I have tried adding in the range of scores via different syntax (10,11,12,13,14) and "10", "11", "12", "13", "14" and have tried
=COUNTIF(Score:Score, "10", Score:Score, "11", Score:Score, "12" etc
I was wondering if there was a way to select "between 10 and 14" as an example which might make the formula easier?
As always, grateful of any help
Thanks
Phil
Comments

Brute force method would be to create another column (named ScoreCat for example) that has a set of nested IF formulae, and then categorises according to the range e.g. =IF(Score1 < 11, 1, IF(Score1 > 19, 3, 2)). You'll need to fill this formula down over all the rows in that column. Then you can use the COUNTIF to count the number of each category e.g. =COUNTIF(ScoreCat:ScoreCat, 3) for the high risks etc.
Happy Smartsheeting
Peter

Hi Phil, try this formula!
=COUNTIFS(Score:Score, ">" + 9, Score:Score, "<" + 15)
This uses a COUNTIFS to look for multiple criteria. This will count any number thats between 1014.
You can also concatenate to add text at the end:
=COUNTIFS(Score:Score, ">" + 9, Score:Score, "<" + 15) + " Yellow Risks"
Example result: 3 Yellow Risks

FAB!  Works a treat, thanks Travis.
KR
Phil