How to Count Cells Between Two Values in Google Sheets: A Step-by-Step Guide

The COUNTIFS Function: Handling Multiple Conditions

The core principle behind counting cells between two numbers is the requirement for simultaneous satisfaction of two distinct conditions. A single cell must be both greater than the lower limit AND less than the upper limit. Because the standard COUNTIF function only accepts one criteria/range pair, we must use COUNTIFS, which is specifically designed to handle multiple sets of criteria. This flexibility ensures that the count returned is accurate and reflects only those values that strictly adhere to both boundaries defined by the user.

When constructing the COUNTIFS formula, you must specify the target column (the range of cells) twice, once for the lower bound criteria and once for the upper bound criteria. For instance, if you are working with data in column A, the formula structure will look like this: COUNTIFS(A:A, Criteria_1, A:A, Criteria_2). This redundancy is essential because the function needs to confirm that every value in column A meets the first criteria (e.g., >10) and also meets the second criteria (e.g., <20).

It is critical to remember the proper syntax for defining the numerical criteria within the formula. The operator (such as >, <, >=, or <=) must be concatenated with the numerical value and enclosed within double quotation marks (e.g., ">10"). Failure to properly quote the criteria, especially when using comparison operators, will lead to a formula error or an incorrect result. Mastery of this specific syntax is fundamental to leveraging the full power of conditional counting in Google Sheets.

Basic Syntax for Counting Between Two Values

To quickly count the number of cells falling between a lower value (e.g., 10) and an upper value (e.g., 15) in a designated column within Google Sheets, you can employ the following basic syntax using the COUNTIFS function. Note that this example performs an exclusive count, meaning the numbers 10 and 15 themselves will not be included in the final tally.

=COUNTIFS(A:A,">10",A:A,"<15")

This particular formula effectively counts the number of cells within the entirety of column A that satisfy two simultaneous criteria: the value must be strictly greater than 10, and simultaneously, the value must be strictly less than 15. The result will include numbers like 10.01 up to 14.99, but it will exclude the boundaries 10 and 15.

The power of COUNTIFS lies in its logical structure, which utilizes the implicit AND operator between the pairs of range and criteria. Since both conditions must be true for the cell to be counted, we successfully isolate the target subset of data. This approach is highly flexible and can be adapted to any column simply by replacing A:A with the desired range of cells (e.g., C2:C50 or D:D).

Practical Example 1: Exclusive Counting (Greater Than and Less Than)

Let us examine a concrete scenario to illustrate the application of the COUNTIFS function for exclusive counting. Suppose we have a dataset tracking scores or product points, and we are interested in counting how many entries fall into a specific performance band, such as scores strictly between 10 and 20.

Suppose we begin with the following raw data structured in Google Sheets, where column B holds the ‘Points’ we wish to analyze:

Our objective is to count the number of cells in column B that have a value strictly greater than 10 and strictly less than 20. This means that 10 and 20 are excluded from the count. We utilize the > and < comparison operators, formatted precisely within the quoted criteria strings, applied sequentially to the range B:B.

We will place the following formula into an empty cell (e.g., cell D2) to execute the conditional count:

=COUNTIFS(B:B,">10",B:B,"<20")

The subsequent screenshot demonstrates the result of implementing this COUNTIFS formula directly into the spreadsheet environment. As shown, the formula successfully isolates and counts only those values that strictly meet both the lower and upper bounds of our defined criteria.

Upon execution, the formula returns a count of 4. This indicates that there are exactly four points values within column B that are simultaneously greater than 10 and less than 20. These four values represent the elements that satisfy the exclusive range condition we established.

Verifying the Exclusive Count

To ensure the accuracy of the COUNTIFS output, it is always beneficial to manually cross-check the data against the defined criteria. In our dataset, the criteria were Points > 10 and Points < 20. We must look through column B and identify entries that fit this specific condition.

The values present in column B are: 10, 12, 22, 14, 20, 18, 11, and 25. Let’s evaluate each one against the criteria:

• 10: Not greater than 10. (Excluded)
• 12: Greater than 10 AND less than 20. (Included)
• 22: Not less than 20. (Excluded)
• 14: Greater than 10 AND less than 20. (Included)
• 20: Not less than 20. (Excluded)
• 18: Greater than 10 AND less than 20. (Included)
• 11: Greater than 10 AND less than 20. (Included)
• 25: Not less than 20. (Excluded)

As confirmed by the manual inspection, the qualifying values are 12, 14, 18, and 11, resulting in a total count of 4. This manual verification confirms the precision of the COUNTIFS function when using strict comparison operators (> and <).

The visual representation below further highlights the four specific entries that satisfied our exclusive range count:

Practical Example 2: Inclusive Counting (Greater Than or Equal To)

Often, data analysis requires counting boundary values inclusively. For instance, if a score of 10 is considered the minimum passing grade and 20 the maximum acceptable grade, we need to include both 10 and 20 in our count. To achieve this, we simply substitute the strict operators (> and <) with the inclusive operators: >= (greater than or equal to) and <= (less than or equal to).

Using the same dataset in column B, we now wish to count the number of cells that have a value greater than or equal to 10 AND less than or equal to 20. This adjustment ensures that any cell containing exactly 10 or exactly 20 is now included in the final tally. This method is essential when defining precise, closed intervals in statistical or quality control applications.

We must use >= and <= within the criteria strings to count the values between 10 and 20, inclusive. The structure remains identical, focusing on the careful placement of the inclusive operators within the second and fourth arguments of the COUNTIFS function:

=COUNTIFS(B:B,">=10",B:B,"<=20")

The following screenshot shows the application of this inclusive formula in practice, demonstrating the increased count due to the inclusion of the boundary values.

The result, displayed in cell D3, clearly reflects the successful application of the inclusive boundary conditions:

In this scenario, the formula correctly returns 6. This is because we have now included the two boundary points, 10 and 20, in addition to the four values previously counted (12, 14, 18, 11). This demonstrates the crucial impact of selecting the correct comparison operator when defining your numerical criteria.

Manual Verification of the Inclusive Count

To confirm the result of 6, we revisit the data in column B and apply the new inclusive criteria: Points >= 10 and Points <= 20. This rigorous manual check reinforces the understanding of how the inclusive operators function within the COUNTIFS structure.

Re-evaluating the values in column B (10, 12, 22, 14, 20, 18, 11, and 25):

• 10: Greater than or equal to 10 AND less than or equal to 20. (Included)
• 12: Greater than or equal to 10 AND less than or equal to 20. (Included)
• 22: Not less than or equal to 20. (Excluded)
• 14: Greater than or equal to 10 AND less than or equal to 20. (Included)
• 20: Greater than or equal to 10 AND less than or equal to 20. (Included)
• 18: Greater than or equal to 10 AND less than or equal to 20. (Included)
• 11: Greater than or equal to 10 AND less than or equal to 20. (Included)
• 25: Not less than or equal to 20. (Excluded)

The total count of qualifying entries is indeed 6, comprising the numbers 10, 12, 14, 20, 18, and 11. The visual aid below clearly identifies these six values within the dataset.

This confirms that by changing the operators from strict inequality (>, <) to non-strict inequality (>=, <=), we successfully modified the range definition from an open interval to a closed interval, thereby accurately counting all points including the specified boundaries.

Handling Dynamic Criteria Using Cell References

While hardcoding the values (like ">10") into the formula works for static analysis, real-world data manipulation often requires dynamic criteria based on user input or calculated variables in other cells. Instead of embedding the numbers directly, you can link the criteria to specific cell references in Google Sheets.

To implement dynamic criteria, you must use the concatenation operator (&) to combine the comparison symbol (which must remain in quotes) with the cell reference. For example, if your lower bound (10) is stored in cell D1 and your upper bound (20) is stored in cell D2, the dynamic formula structure would look like this:

=COUNTIFS(B:B,">="&D1,B:B,"<="&D2)

This method offers immense flexibility. If the user changes the values in cells D1 or D2, the COUNTIFS formula immediately updates the results without requiring any alteration to the formula itself. This is highly recommended for building robust, user-friendly dashboards and analytical tools where boundaries might frequently change based on new requirements or datasets.

Key Differences Between COUNTIF and COUNTIFS

While COUNTIF is a foundational function, it is essential to emphasize why COUNTIFS is the necessary function for counting between two values. The fundamental difference lies in the number of condition pairs each function can process.

1. COUNTIF: Designed to evaluate a single range against a single criterion. It uses a single Range, Criteria pair. It is perfect for simple counts, such as counting all values greater than 50.
2. COUNTIFS: Designed to evaluate multiple ranges against multiple criteria. It can handle up to 127 pairs of Range, Criteria. When counting between two values, we impose two separate criteria (lower bound AND upper bound) on the same range of cells, thus requiring the multi-criteria capability of COUNTIFS.

Attempting to use COUNTIF for a two-value count would result in complex, non-standard array formulas or, more likely, an inaccurate result, as COUNTIF is not inherently structured to evaluate an “AND” relationship across two different boundaries within the same column. Therefore, COUNTIFS is the definitive and straightforward solution for this specific counting problem in Google Sheets.

Conclusion and Further Resources

The COUNTIFS function provides an exceptionally powerful yet simple mechanism for analyzing quantitative data by defining specific numerical ranges. Whether you require exclusive counting (using > and <) or inclusive counting (using >= and <=), the fundamental requirement remains applying two distinct criteria pairs to the same target range of cells.

Mastering the application of comparison operators and understanding the structure of multi-criteria functions like COUNTIFS is crucial for anyone performing intermediate to advanced data analysis within spreadsheet environments. Always verify your criteria format, especially ensuring that operators are correctly enclosed in quotes, to guarantee accurate results.

Note: You can find the complete documentation for the COUNTIFS function in Google Sheets here: COUNTIFS Official Documentation.

If you are looking to expand your knowledge of conditional operations, the following tutorials explain how to perform other common COUNTIF() operations in Google Sheets, such as counting based on text strings, dates, or unique values:

• How to use COUNTIF with text criteria.
• Using COUNTIF to count dates within a specific timeframe.
• Combining array formulas with COUNTIF for complex data evaluation.