Count Excel Formulas

Count cells between two numbers in Excel

CA Anshuman Tandon

When you’re analysing data in Excel, a very common task is to find how many values fall inside a specific numeric range. Maybe you’re grouping test scores, categorising sales amounts, or building bands like 0–49, 50–69, 70–79, etc. Counting cells between two numbers in a clean, repeatable way can save a lot of manual filtering and eyeballing.

This guide walks through how to do that with COUNTIFS, a simple SUMPRODUCT alternative, and even briefly shows how the same range logic can drive a FILTER formula. The tone is practical and beginner‑friendly, but accurate enough for real reports.

What “between two numbers” really mean

Conceptually, counting cells between two numbers is straightforward:

  • You have a numeric range, for example C5:C16.
  • For each row, you want to know if the value is greater than or equal to a lower boundary and less than or equal to an upper boundary.
  • Only values which satisfy both tests should be counted.

So for one band, the logic is:

  • value >= Start
  • value <= End

In real life, you might repeat this for many ranges: 0–49, 50–59, 60–69, and so on.

Using COUNTIFS to count numbers between two values

COUNTIFS is designed to count cells that meet multiple conditions, which makes it a perfect match for “between two numbers”.​

Example setup

Imagine this layout:

  • Numbers you want to analyse are in C5:C16.
  • You create a named range data that refers to C5:C16.
  • Column E contains Start values (e.g., 70, 80, 90).
  • Column F contains End values that match each band (e.g., 79, 89, 99).
  • In column G you want the count for each band.

The core COUNTIFS formula in G5 looks like:

text=COUNTIFS(data, ">=" & E5, data, "<=" & F5)

How this works:

  • data, ">=" & E5 keeps only numbers at least as large as the value in E5.​
  • data, "<=" & F5 keeps only numbers at most as large as the value in F5.​
  • Because both conditions use the same range data, each cell must pass both tests to be counted.​

Copy the formula down column G and each row returns the count of numbers that fall within the band defined by that row’s Start and End.

Why are the operators in quotes?

You might notice the slightly odd syntax ">=" & E5 instead of just >=E5. This is one of those quirks of the COUNTIFS / SUMIFS family:

  • The comparison operator like ">=" must be written as text, so it goes inside quotes.​
  • The cell reference (E5) is then joined to that text using & (concatenation).​

So inside the function, Excel effectively sees criteria like ">=70" for one band and "< =79" for the same band’s upper limit.

SUMPRODUCT alternative for number ranges

If you prefer a formula that reads more like direct logic and don’t want to bother with quoted operators, SUMPRODUCT is a great alternative. It handles array operations natively and uses a more familiar comparison style.

Using the same named range data and Start/End values in E5 and F5, you can write:

text=SUMPRODUCT((data >= E5) * (data <= F5))

What’s going on:

  • (data >= E5) returns an internal array of TRUE/FALSE values, one for each cell in data.
  • (data <= F5) does the same for the upper bound.
  • Multiplying the two arrays coerces TRUE to 1 and FALSE to 0, so only values that meet both conditions produce a 1.

For example, if data has 12 values, each comparison creates 12 TRUE/FALSE results. When multiplied, you end up with something like:

  • {1;0;0;0;1;0;0;0;1;0;1;0}

SUMPRODUCT simply adds these 1s and returns the total count of values in that specific numeric range.

In real life, this might sound a bit “mathy” at first, but after you use it a couple of times, the pattern becomes very natural.

Practical use case: score bands

As a concrete example, imagine you have a list of exam scores in C5:C16, and you want to know how many students fall into each band:

  • 0–59 (Fail)
  • 60–69 (Average)
  • 70–79 (Good)
  • 80–89 (Very Good)
  • 90–100 (Excellent)

You could set up:

  • Column E: 0, 60, 70, 80, 90 (Start).
  • Column F: 59, 69, 79, 89, 100 (End).
  • Column G: the count of scores in each band.

Then either of these in G5, copied down:

Using COUNTIFS:

text=COUNTIFS(data, ">=" & E5, data, "<=" & F5)

Using SUMPRODUCT:

text=SUMPRODUCT((data >= E5) * (data <= F5))

Both approaches will tell you how many scores fall into each band, and you can then build charts or conditional formatting from that summary.

Reusing the same logic with FILTER

One neat benefit of the SUMPRODUCT‑style logic is that the same condition (data >= E5) * (data <= F5) can be reused in other modern functions like FILTER.

If your version of Excel supports dynamic array functions, you can actually return all numbers in the range instead of just a count. For example, to spill out all values between the Start and End in row 5:

text=FILTER(data, (data >= E5) * (data <= F5))

Here:

  • (data >= E5) * (data <= F5) again produces an array of 1s and 0s.
  • FILTER uses that array to decide which rows to include in the result.

So instead of only seeing “4 values in the 70–79 band”, you would actually see the list of those four values. That’s handy when you want to audit or spot‑check the raw data behind your counts.

Tips, best practices, and common mistakes

A few small things to keep in mind so your formulas behave as expected:

  • Use numeric ranges: ensure your data range really contains numbers, not numbers stored as text; otherwise comparisons may return unexpected results.​
  • Inclusive vs exclusive ranges: the example formulas use >= for the Start and <= for the End, which means both boundaries are included. If you want “Start inclusive, End exclusive”, swap <= for <.
  • Keep ranges the same size: in COUNTIFS, all range arguments (like data) must be the same size, or you’ll get a #VALUE! error.​
  • Choose the pattern you understand best: COUNTIFS is more verbal (“greater than or equal to Start”), while SUMPRODUCT is more algebraic. Either is fine; use the one that future‑you will find easier to read.

Key takeaways and what to try next

Counting cells between two numbers in Excel boils down to applying two conditions to the same numeric range: one for the lower bound, one for the upper bound. COUNTIFS does this with range/criteria pairs and concatenated operators; SUMPRODUCT does it with direct logical expressions and Boolean logic.​

To put this into practice, take any real dataset—scores, sales, or measurements—and set up a small Start/End table of ranges you care about. Then, use one formula in the first row (COUNTIFS or SUMPRODUCT) and copy it down to get an instant banded summary. Once you’re comfortable, try using the same conditions with FILTER to pull out the actual values in each range, not just their counts.

Tags:

Related tutorials