How to Count Values Between Two Numbers in Excel

Manually segmenting and analyzing high-volume dataset thresholds can be incredibly tedious and error-prone. While organizations often rely on standard funding sources to drive projects, tracking these specific allocations requires dynamic reporting.

Implementing the COUNTIFS formula grants users immediate analytical clarity, transforming raw data into actionable insights. However, as an important stipulation, logical operators must be enclosed in quotation marks to execute correctly. For example, counting regional grant distributions between $10,000 and $50,000 becomes entirely automated.

Below, we break down the exact formula syntax and step-by-step configuration to master this range-counting technique.

Whether you are analyzing sales figures, grading student exams, tracking inventory levels, or monitoring project timelines, you will frequently find yourself needing to count how many data points fall within a specific numerical range. For instance, you might want to know how many transactions were between $100 and $500, or how many students scored between 70% and 85%.

In Microsoft Excel, there are several powerful ways to accomplish this. While the COUNTIFS function is the most modern and widely recommended tool for the job, alternative approaches like SUMPRODUCT and the classic COUNTIF subtraction method offer unique advantages depending on your specific version of Excel and your data layout. In this comprehensive guide, we will explore these methods in detail, step-by-step, with practical examples.

Setting Up a Sample Dataset

To make our examples easy to follow, let us assume we have a simple Excel spreadsheet containing student test scores. The data is located in the range B2:B11, as shown in the table below:

Our goal in the following examples is to count how many student scores fall between 60 and 80, inclusive (meaning we want to include scores that are exactly 60 and exactly 80).

Method 1: The COUNTIFS Function (Best Practice)

The COUNTIFS function is designed specifically to count cells that meet multiple criteria across one or more ranges. Introduced in Excel 2007, it has become the gold standard for counting values within a range.

The Syntax

The basic syntax for COUNTIFS is:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

1. Using Hardcoded Values

If you want to plug the boundary numbers directly into your formula, you must enclose the logical operators and numbers in double quotation marks. To count values between 60 and 80 (inclusive), use this formula:

=COUNTIFS(B2:B11, ">=60", B2:B11, "<=80")

How it works:

• B2:B11, ">=60" checks the range and keeps track of all cells with values greater than or equal to 60.
• B2:B11, "<=80" checks the exact same range and filters for cells with values less than or equal to 80.
• COUNTIFS returns the count of cells that satisfy both conditions simultaneously. In our sample dataset, the scores 67, 72, and 80 meet these conditions, so the formula will return 3.

2. Using Cell References (Dynamic Criteria)

Hardcoding values inside formulas is generally discouraged because it makes your spreadsheets difficult to update. Instead, you should store your lower and upper boundaries in separate cells. Let's assume you put your lower limit (60) in cell D2 and your upper limit (80) in cell E2.

To reference these cells, you must concatenate the comparison operators with the cell references using the ampersand (&) symbol:

=COUNTIFS(B2:B11, ">=" & D2, B2:B11, "<=" & E2)

Crucial Tip: Notice that only the logical operators (">=" and "<=") are inside quotation marks. The ampersand joins the operator to the value inside the referenced cell. If you write ">=D2", Excel will literally search for the text "D2" instead of evaluating the value stored in the cell.

3. Exclusive vs. Inclusive Ranges

Adjusting whether you include or exclude the boundary values is simple. You just need to alter your comparison operators:

• Inclusive (includes boundaries): Use ">=" and "<=".
• Exclusive (excludes boundaries): Use ">" and "<".

Method 2: The SUMPRODUCT Function (For Advanced Scenarios)

While COUNTIFS is highly efficient, there are times when you need to perform more complex calculations or work with arrays that do not reside directly in worksheet cells. In these scenarios, the versatile SUMPRODUCT function is an excellent alternative.

The Formula

=SUMPRODUCT((B2:B11>=60) * (B2:B11<=80))

How It Works

SUMPRODUCT works by evaluating expressions as arrays of TRUE and FALSE values, multiplying them together, and then summing the results:

1. The expression (B2:B11>=60) evaluates each cell in the range and returns an array of TRUE and FALSE values:
   {FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, TRUE}
2. The expression (B2:B11<=80) evaluates the same range and returns another array:
   {TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE}
3. In Excel, when you perform mathematical operations (like multiplication *) on boolean values, TRUE is treated as 1 and FALSE is treated as 0.
4. Multiplying these two arrays together element-by-element yields:
   {0*1, 0*1, 1*1, 1*1, 1*0, 1*0, 1*0, 0*1, 0*1, 1*1} which simplifies to
   {0, 0, 1, 1, 0, 0, 0, 0, 0, 1}.
5. Finally, SUMPRODUCT sums the numbers in this final array: 0 + 0 + 1 + 1 + 0 + 0 + 0 + 0 + 0 + 1 = 3.

This method is highly robust and can easily be adapted to handle blank cells, error values, or criteria spanning different worksheets and complex array operations.

Method 3: The COUNTIF Subtraction Method (Legacy Approach)

Before the introduction of COUNTIFS in Excel 2007, users had to rely on a mathematical workaround using the single-criterion COUNTIF function. While it is less intuitive, understanding this method is helpful for maintaining older spreadsheets.

The Logic

The core concept is simple subtraction: find the total number of values that are less than or equal to your upper boundary, and subtract the number of values that are strictly less than your lower boundary.

The Formula

=COUNTIF(B2:B11, "<=80") - COUNTIF(B2:B11, "<60")

How It Works

• COUNTIF(B2:B11, "<=80") counts all scores that are 80 or lower. In our dataset, this returns 6 (scores: 45, 58, 67, 72, 40, 55, 80).
• COUNTIF(B2:B11, "<60") counts all scores that are strictly less than 60. In our dataset, this returns 3 (scores: 45, 40, 55).
• Subtracting the second group from the first (6 - 3) isolates the values that fall precisely between 60 and 80, giving us our final answer of 3.

Counting Dates Between Two Dates

Excel stores dates as sequential serial numbers, meaning that these exact same principles apply to date ranges. If you want to count the number of projects completed between January 1, 2023, and December 31, 2023, you can use COUNTIFS in the exact same manner.

Assuming your dates are in the range C2:C11:

=COUNTIFS(C2:C11, ">=2023-01-01", C2:C11, "<=2023-12-31")

Or dynamically referencing cells D2 (start date) and E2 (end date):

=COUNTIFS(C2:C11, ">=" & D2, C2:C11, "<=" & E2)

Summary: Choosing the Right Formula

• Use COUNTIFS for almost all standard situations. It is fast, easy to write, read, and maintain.
• Use SUMPRODUCT if you need to perform calculations on the data before checking the criteria (e.g., using the YEAR function on a date range within the formula), or if you are working with arrays.
• Use the COUNTIF Subtraction Method only if you are working in legacy environments or need to maintain retro-compatibility with incredibly old versions of spreadsheet software.