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

✭✭✭✭
edited 12/09/19

Hi

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

Thanks

Phil

Tags:

• 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

Peter

• 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

• ✭✭✭✭

FAB! - Works a treat, thanks Travis.

KR

Phil

This discussion has been closed.