Count of multiple values

Phil Smith

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

   

 

Peter Kirkham

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

Travis

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.

 

KR

 

Phil