How to / Counting
How to count cells between two numbers in Excel
Use =COUNTIFS(A2:A6, ">=60", A2:A6, "<=90"): the same range twice, once for the lower limit and once for the upper.
Scores from 60 to 90
| A | B | C | |
|---|---|---|---|
| 1 | Score | ||
| 2 | 45 | 3 | |
| 3 | 72 | ||
| 4 | 88 | ||
| 5 | 60 | ||
| 6 | 95 |
In C2: =COUNTIFS(A2:A6, ">=60", A2:A6, "<=90") → 3
72, 88 and 60.
How it works
- COUNTIFS counts the rows that meet every condition it is given.
- Each condition is a range followed by a test in quotes.
- Using the same range for both tests means "at least 60 and at most 90".
Variations
Not including the limits
A B C 1 Score 2 45 2 3 72 4 88 5 60 6 95 In C2:
=COUNTIFS(A2:A6, ">60", A2:A6, "<90")→ 2Drop the equals signs and 60 itself is no longer counted.
Add them up instead
A B C 1 Score 2 45 220 3 72 4 88 5 60 6 95 In C2:
=SUMIFS(A2:A6, A2:A6, ">=60", A2:A6, "<=90")→ 220In any version, with SUMPRODUCT
A B C 1 Score 2 45 3 3 72 4 88 5 60 6 95 In C2:
=SUMPRODUCT((A2:A6>=60)*(A2:A6<=90))→ 3
Watch out for
- Limits kept in cells. To take the limits from cells, join them to the operator with &:
=COUNTIFS(A2:A6, ">="&D1, A2:A6, "<="&D2).">=D1"in one piece looks for the text D1. - Dates work the same way. Dates are numbers, so the same formula counts the dates between two dates.
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- COUNTIFS: Counts the rows that meet every one of several conditions.
- COUNTIF: Counts the cells that meet a condition.
- SUMIFS: Adds the cells that meet every one of several conditions.
- SUMPRODUCT: Multiplies matching items in arrays, then adds the results.