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.
Next with premium:VLOOKUPINDEX and MATCH
Need help?
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.