How to count values in ranges like age groups in Excel
Formula (Excel and Google Sheets)
=COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&F2)List each group's lower and upper bound and count between them: =COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&F2), filled down.
- Result on the example
- one result per row in
G2, filled down toG5 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G2, filled down to G5. Change any input and the result updates, or edit the formula to experiment.
How it works
Each row of the small table is one group, with its bounds in E and F. COUNTIFS counts ages on or above the lower bound and on or below the upper bound. The ages are locked with $ so every row counts the same list, while E2 and F2 move down to the next group.
The four counts add up to all 12 members, a quick check that the groups don't overlap or leave gaps.
=COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&F2)Result3
$B$2:$B$1312 rows × 1 col: 23, 31, 45, 19, …A range: 12 cells in one column.
E218The value in E2.
">="&E2">=18"The & operator joins as text.
F229The value in F2.
"<="&F2"<=29"The & operator joins as text.
COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&F2)3COUNTIFS: Counts rows that meet every condition.- criteria_range1
$B$2:$B$1312 rows × 1 col: 23, 31, 45, 19, … - criteria1
">="&E2">=18" - criteria_range2
$B$2:$B$1312 rows × 1 col: 23, 31, 45, 19, … - criteria2
"<="&F2"<=29"
- criteria_range1
In Google Sheets
The same formula works in Google Sheets: =COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&F2). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(($B$2:$B$13>=E2)*($B$2:$B$13<=F2)) | 3, 4, 3… | The same count as an array test. |
Common mistakes
- With decimals (ages like 29.5 or prices), "to 29" and "from 30" leave a gap. Use only lower bounds and "<" the next group's lower bound instead.
- Check that the counts add up to the total; a missing or overlapping range shows up immediately.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count values in ranges like age groups
G2, count the members whose age is from E2 to F2, then fill down to G5.Questions
Can FREQUENCY do this?
Yes, in one formula: =FREQUENCY(B2:B13,{29,44,59}) returns four counts, for up to 29, 30-44, 45-59 and over 59.
How do I do this in a pivot table?
Put Age in Rows, right-click it, choose Group, and set the start, end and step (for example 15 years).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.