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

ABC
1Score
2453
372
488
560
695

In C2: =COUNTIFS(A2:A6, ">=60", A2:A6, "<=90") → 3

72, 88 and 60.

How it works

  1. COUNTIFS counts the rows that meet every condition it is given.
  2. Each condition is a range followed by a test in quotes.
  3. Using the same range for both tests means "at least 60 and at most 90".

Variations

  1. Not including the limits

    ABC
    1Score
    2452
    372
    488
    560
    695

    In C2: =COUNTIFS(A2:A6, ">60", A2:A6, "<90") → 2

    Drop the equals signs and 60 itself is no longer counted.

  2. Add them up instead

    ABC
    1Score
    245220
    372
    488
    560
    695

    In C2: =SUMIFS(A2:A6, A2:A6, ">=60", A2:A6, "<=90") → 220

  3. In any version, with SUMPRODUCT

    ABC
    1Score
    2453
    372
    488
    560
    695

    In C2: =SUMPRODUCT((A2:A6>=60)*(A2:A6<=90)) → 3

Watch out for

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

Practise it

Related