# How to count values in ranges like age groups in Excel

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.

**Excel:** `=COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&F2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Member | Age |  | Group | From | To | Members |
| M-01 | 23 |  | 18-29 | 18 | 29 |  |
| M-02 | 31 |  | 30-44 | 30 | 44 |  |
| M-03 | 45 |  | 45-59 | 45 | 59 |  |
| M-04 | 19 |  | 60+ | 60 | 120 |  |
| M-05 | 52 |  |  |  |  |  |
| M-06 | 38 |  |  |  |  |  |
| M-07 | 61 |  |  |  |  |  |
| M-08 | 29 |  |  |  |  |  |
| M-09 | 44 |  |  |  |  |  |
| M-10 | 67 |  |  |  |  |  |
| M-11 | 35 |  |  |  |  |  |
| M-12 | 58 |  |  |  |  |  |

Formula in G2, filled to G5: `=COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&F2)`

Result: 3; 4; 3; 2

## 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.

## Other ways

- `=SUMPRODUCT(($B$2:$B$13>=E2)*($B$2:$B$13<=F2))`: 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.

## 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).

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-values-in-ranges-frequency-table