Count Between Two Numbers in Excel using Simple Formula!

Sharing is caring!

Working with data often requires counting how many values fall between two numbers. Excel makes this task easy with built-in functions and logical formulas. Whether you’re analyzing sales data, student scores, or survey responses, understanding how to count between two values in Excel can save time and improve accuracy.

In this guide, we will explain multiple methods using simple functions like COUNTIF, COUNTIFS, and array formulas. You’ll also learn tips to handle dates, ranges, and conditional logic.

Example Scenario

Before writing any formula, it’s important to clarify what we mean by “count between two numbers.”

You have a list of numbers in Column A, and you want to count how many of them are greater than or equal to 50 and less than or equal to 100.

Method 1: Using COUNTIFS Function to Count Between Two Numbers

The COUNTIFS function is the best method to count values between two numbers.

Syntax

COUNTIFS(range, ">=lower_limit", range, "<=upper_limit")

Example

Assume your numbers are in cells A2 to A20.

=COUNTIFS(A2:A20, ">=50", A2:A20, "<=100")

This formula counts how many numbers in A2:A20 are between 50 and 100 (inclusive).

Table Example

Formula: =COUNTIFS(A2:A7, ">=50", A2:A7, "<=100")
Result: 3 (52, 99, 100)

COUNTIFS is case-insensitive, works with numbers and dates, and supports multiple conditions.

Method 2: Using COUNTIF with Subtraction

If you’re using an older version of Excel that doesn’t support COUNTIFS, you can combine two COUNTIF functions:

Formula Structure

=COUNTIF(range, "<=upper_limit") - COUNTIF(range, "<lower_limit")

This counts values greater than lower_limit and less than or equal to upper_limit.

Example

=COUNTIF(A2:A20, "<=100") - COUNTIF(A2:A20, "<50")

This gives the count of values from 50 to 100, excluding anything less than 50.

Method 3: Using SUMPRODUCT for Flexible Logic

If you want more control over conditions, you can use SUMPRODUCT.

Syntax

=SUMPRODUCT((range>=lower_limit)*(range<=upper_limit))

This method evaluates each value in the range and returns the count of those that match both conditions.

Example

=SUMPRODUCT((A2:A20>=50)*(A2:A20<=100))

This returns the same result as the COUNTIFS example above.

When to Use

• If you’re working with dynamic conditions
• If you need to nest logical expressions
• For arrays that COUNTIFS might not handle well

Method 4: Array Formula with IF and SUM

Another method is using an array formula with IF and SUM.

Formula

=SUM(IF((A2:A20>=50)*(A2:A20<=100), 1, 0))

This returns the same result as the earlier formulas but must be entered as an array formula (press Ctrl+Shift+Enter in Excel 2016 or earlier).

In Excel 365 or Excel 2019, you can enter it normally because these versions support dynamic arrays.

Counting Between Two Dates

Just like with numbers, Excel allows you to count dates that fall between two specific values using the COUNTIFS function. This is especially useful for tracking records like attendance, sales, or project deadlines.

You have a list of dates in cells A2 to A20 and want to count how many of them are between January 1, 2024, and January 31, 2024, inclusive.

Static Formula (Hardcoded Dates)

=COUNTIFS(A2:A20, ">=01/01/2024", A2:A20, "<=01/31/2024")

This counts all dates in the range A2:A20 that are on or after January 1, 2024, and on or before January 31, 2024.

Dynamic Formula (Using Cell References)

Instead of hardcoding the dates, you can store them in two separate cells:

Then use this formula:

=COUNTIFS(A2:A20, ">="&C1, A2:A20, "<="&D1)

This version is more flexible—you can change the start and end dates in C1 and D1 without updating the formula itself.

Table Example

Using either formula above:

Result: 3
(Only 01/05/2024, 01/15/2024, and 01/31/2024 are within the specified date range.)

Using Named Ranges for Readability

For better readability, define a named range (e.g., MyRange) for your data.

Then use:

=COUNTIFS(MyRange, ">=50", MyRange, "<=100")

This makes the formula easier to maintain, especially in large worksheets.

Counting Between Two Numbers with OR Logic

What if you want to count numbers that are less than 50 OR greater than 100?

You’ll need to combine two COUNTIF formulas:

=COUNTIF(A2:A20, "<50") + COUNTIF(A2:A20, ">100")

This counts all values that are outside the range of 50–100.

Dynamic Lower and Upper Limits

To make your formula dynamic, store lower and upper values in cells.

Example

Formula:

=COUNTIFS(A2:A20, ">="&C1, A2:A20, "<="&D1)

This is useful when users want to change the filter values without editing the formula.

Counting with Filters

If you’re using Excel AutoFilter, it won’t affect the result of COUNTIFS.

If you want to count only visible cells, use this formula:

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A20, ROW(A2:A20)-MIN(ROW(A2:A20)), 0, 1)), --(A2:A20>=50), --(A2:A20<=100))

Here, SUBTOTAL(103,...) checks visibility.

Errors to Avoid

Which Method Should You Use?

Final Tips

• Use absolute references like $A$2:$A$20 if you’re copying formulas.
• Make formulas dynamic by referencing cell values instead of hard-coding numbers.
• Keep your data clean—no blank rows, no merged cells.
• Use conditional formatting to visually highlight numbers between the two limits.

Counting values between two numbers in Excel is a common task that becomes easy when you understand the right function to use. Whether you’re using COUNTIFS, SUMPRODUCT, or a simple subtraction of COUNTIF results, you can tailor your formula to meet different scenarios.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.