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

Phil Smith
edited 12/09/19 in Archived 2016 Posts



I'm workng on a risk log template and want to summarise the number of red risks (score 20), amber risks (score 15-19), number of yellow risks (score 10-14) 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









  • Peter Kirkham
    edited 01/15/16

    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 Smile


  • Travis
    Travis Employee

    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 10-14.


    You can also concatenate to add text at the end:


    =COUNTIFS(Score:Score, ">" + 9, Score:Score, "<" + 15) + " Yellow Risks"


    Example result: 3 Yellow Risks

  • Phil Smith

    FAB! - Works a treat, thanks Travis.





This discussion has been closed.