Select Page

Imagine that you have a list of numbers and you need an interval-based count. To give you an example…you have 1000 rows of employee-related data including a column containing each person’s age. You need to know how many people are aged between 16 and 18; between 19 and 25; between 26 and 40; between 41 and 50 and over 50.

Pivot Tables allow you to group numerical data but the interval has to be consistent, e.g. 16-20; 21-25; 26-30 and so on, so that won’t work here.

Another option is to use the COUNTIFS function, or to be more exact, use multiple COUNTIFS functions:

=COUNTIFS(E2:E1001, “>=16”, E2:E1001, “<=18”)

=COUNTIFS(E2:E1001, “>=19”, E2:E1001, “<=25”)

=COUNTIFS(E2:E1001, “>=26”, E2:E1001, “<=40”)

=COUNTIFS(E2:E1001, “>=41”, E2:E1001, “<=50”)

=COUNTIFS(E2:E1001, “>50”)

So COUNTIFS will work but you need to create multiple functions.

A much quicker way is to use a single instance of the FREQUENCY function which is what I have done in the screenshot above. In this video I explain what the FREQUENCY function is and how to use it.

You can download a copy of the file I used in the video from the YouTube description