Formula to count group numbers into ranges

Example you have a table like that:

1 Student Name Score
2 Bob Jone 5
3 Ayako Tanaka 6
4 Po Quant 9
5 Sue bryce 4
6 andy samberg 3
7 Tan chang 8
8 bob marley 7
9 renee zellweger 10

And you want to count how many students in the class have a score greater than 5 and less than 8.

In this case, I use formula: =COUNTIFS(A2,">5",A9,"<7")

The result is two because just Sue Bryce and Andy Samberg are satisfied conditions ( a score greater than 5 and less than 8).

To group numeric data into ranges, you can build a summary table and use COUNTIFS to count values at each threshold.

We have general formula: =COUNTIFS(range,">=low",ages,"<=high")

  • range‘ represents a range of cells that contain numbers (Example: A2:A9)
  • Low and high are term