Count between two numbers with COUNTIFS
PremiumIn E2, use COUNTIFS to count the people in B2:B8 aged 18 to 30, both ages included. Use the same range twice, once for each limit.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Age | ||||
| 2 | Ali | 17 | Aged 18 to 30 | |||
| 3 | Bo | 22 | ||||
| 4 | Cy | 31 | ||||
| 5 | Di | 30 | ||||
| 6 | Ed | 18 | ||||
| 7 | Fi | 45 | ||||
| 8 | Gu | 26 | ||||
| 9 |
Premium lesson
Unlock COUNTIFS and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test for job interviews
Your first month for $9.50 $19
50% off your first month when you join today.
Then $19/month. Cancel any time, and get a full refund within 14 days if it isn't for you.
Why this works
To count values between two limits, give COUNTIFS the same range twice: once with the lower limit and once with the upper. 22, 30, 18 and 26 are in the band, so the answer is 4.
Common mistake: Using > and < instead of >= and <=. Then 18 and 30 themselves aren't counted.
The answer
- E2
=COUNTIFS(1B2:B8,2">=18",3B2:B8,4"<=30")
In plain words: Count the rows where 1the age in B2:B8 is 218 or more and 3the same age is 430 or less.