COUNTIFS between two values
Count the customers in each age band. Each band has its own limits: from E2 to F2 for the first.
keyboard Typing formulas is easiest on a computer with a keyboard.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Customer | Age | Band | From | To | Customers | ||
| 2 | Yusuf | 48 | 18 to 29 | 18 | 29 | |||
| 3 | Uma | 37 | 30 to 44 | 30 | 44 | |||
| 4 | Felix | 59 | 45 to 59 | 45 | 59 | |||
| 5 | Rosa | 46 | 60+ | 60 | 99 | |||
| 6 | Hiro | 67 | ||||||
| 7 | Amara | 85 | ||||||
| 8 | Jamal | 45 | ||||||
| 9 | Quinn | 59 | ||||||
| 10 | Priya | 51 | ||||||
| 11 | Ben | 44 | ||||||
| 12 | Isla | 83 | ||||||
| 13 | Chloe | 18 | ||||||
| 14 | ||||||||
| 15 | ||||||||
| 16 | ||||||||
| 17 |
How COUNTIFS works
How do you count between two values?
Give
COUNTIFSthe same range twice: once with a lower limit and once with an upper limit. It only counts the cells that pass both.=COUNTIFS(B2:B13,">=30", B2:B13, "<=44") How do you take the limits from cells?
Join the operator to the cell with &, as in the criteria lesson:
=COUNTIFS($B$2:$B$13,">="&E2, $B$2:$B$13, "<="&F2) Now each row of a band table can have its own limits, and one formula copied down counts every band.
Should it be >= or >?
Decide which band an edge value belongs to. With ">=" and "<=", someone aged exactly 30 is counted in the 30 to 44 band. Use ">=" for the lower limit and "<" for the next band's start if your limits touch, such as 0 to 10, 10 to 20.
Does this work with dates?
Yes. Dates are numbers in Excel, so ">="&
E2with a date inE2counts everything on or after it. That is how you count orders in a month.
Know someone fighting with a spreadsheet? Send them this page.