Exceljet

Quick, clean, and to the point

Group numbers into ranges with COUNTIFS

Excel formula: Group numbers into ranges with COUNTIFS
Generic formula 
=COUNTIFS(range,">=low",ages,"<=high")
Explanation 

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

In the example show, we have a list of names and ages and are using the COUNTIFs function to generate a count of ages into 6 brackets. The formula in cell F5 is:

=COUNTIFS(ages,">=20",ages,"<=29")

How this formula works

The named range "ages" refers to C5:C304.

The COUNTIFS function lets you count values that meet multiple criteria with an AND relationship (i.e. all criterial must be true).

In this case, we want to group the data by age range into 10-year brackets, so we use COUNTIFS as follows in column F:

=COUNTIFS(ages,">=20",ages,"<=29") // 20-29
=COUNTIFS(ages,">=30",ages,"<=39") // 30-39
=COUNTIFS(ages,">=40",ages,"<=49") // 40-49

For the final bracket, 70+, we only use one criteria:

=COUNTIFS(ages,">=70") // 70+

Dynamic ranges

To expose ranges values on the worksheet where they can be easily changes, you can join references to logical operators with concatenation like this:

=COUNTIFS(ages,">="$A1,ages,"<="&B1)

This formula counts greater than or equal to (>=) the value in A1 and less than or equal to (>=)the value in B1.

With a Pivot Table

Pivot Tables offer automatic grouping iton ranges. See this video for a similar example.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting work done in Excel. In this step-by-step training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Start building valuable skills with Excel formulas today. Learn more.

Dave, you are an excellent teacher. I've learned so much from your videos (I'm a visual learner), and I appreciate that you provide the text/script for learners who learn best by reading. Your instructions are "pithy"/concise and the length of each video is about all I can absorb at one sitting (depending on the complexity) - Carol
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course