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