To count cells that fall between two numbers in Excel, use COUNTIFS with two criteria: =COUNTIFS(range,">="&low,range,"<="&high). For example, =COUNTIFS(B2:B20,">=10",B2:B20,"<=20") counts how many values in B2:B20 are from 10 to 20 inclusive.
Why COUNTIFS Is the Standard Solution
Excel's COUNTIF function handles a single condition. To check a range between two numbers, you need two conditions: one for the lower bound and one for the upper bound. COUNTIFS accepts multiple criteria pairs and only counts a cell when all conditions are true. This makes it the most direct and widely compatible method across Excel 2010 through Microsoft 365.
The syntax is:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Each criteria range must have the same dimensions. You can use the same range twice, as shown in the opening example. The comparison operators (>=, <=, >, <) must be enclosed in double quotes when combined with a cell reference or number.
How to Write the Formula Step by Step
Assume your numbers are in cells A2:A100, and you want to count values between 50 and 75 inclusive.
- Click an empty cell, for example C1.
- Type:
=COUNTIFS(A2:A100,">=50",A2:A100,"<=75") - Press Enter. The result is the count of cells in A2:A100 that are 50 or greater and 75 or less.
If the lower and upper bounds are stored in cells (say E1 and F1), use cell references:
=COUNTIFS(A2:A100,">="&E1,A2:A100,"<="&F1)
The ampersand (&) joins the operator with the cell value. This makes the formula dynamic—changing E1 or F1 updates the count automatically.
Using COUNTIF for a Single Boundary
Sometimes you only need to count values above a lower limit or below an upper limit. For a single boundary, COUNTIF is simpler:
- Count values greater than 50:
=COUNTIF(A2:A100,">50") - Count values less than or equal to 75:
=COUNTIF(A2:A100,"<=75")
But for a true “between” condition, you must use COUNTIFS or an array formula. COUNTIF alone cannot handle two conditions on the same range.
Alternatives: SUMPRODUCT and FILTER
If you prefer a formula that works in older Excel versions (prior to 2007), SUMPRODUCT is a robust alternative:
=SUMPRODUCT((A2:A100>=50)*(A2:A100<=75))
This multiplies two arrays of TRUE/FALSE values, converting them to 1s and 0s, then sums the result. It returns the same count as COUNTIFS.
In Excel 365 or Excel 2021, you can also use FILTER combined with COUNTA:
=COUNTA(FILTER(A2:A100,(A2:A100>=50)*(A2:A100<=75)))
This filters the range to only the values that meet both conditions and counts the non-empty results. However, COUNTIFS is shorter, faster, and easier to read, so it remains the recommended choice.
Handling Text, Blanks, and Errors
COUNTIFS ignores text and blank cells when counting numeric comparisons. If your range contains numbers stored as text (e.g., “50” with an apostrophe), they will be ignored. To convert text to numbers, select the range and use the warning icon to “Convert to Number,” or use VALUE in a helper column.
If your range contains errors like #DIV/0!, COUNTIFS will ignore them as well. That is usually desirable.
For exclusive bounds (strictly greater than and less than), use > and < without the equals sign:
=COUNTIFS(A2:A100,">50",A2:A100,"<75")
Common Mistakes and How to Avoid Them
- Forgetting the ampersand: Writing
“>=50”is fine, but“>=”&E1is needed for cell references. Without the ampersand, Excel treats “>=E1” as text and returns 0. - Mixing ranges: Both criteria ranges must be the same size. Using A2:A100 for the first and A2:A101 for the second causes a #VALUE! error.
- Using COUNTIF with two criteria: COUNTIF only accepts one condition. Trying
=COUNTIF(A2:A100,">=50",A2:A100,"<=75")returns an error. - Reversing lower and upper: If the lower bound is greater than the upper bound, the formula returns 0. Double-check your cell references.
Frequently Asked Questions
Can I count between two dates? Yes, dates are stored as numbers in Excel. Use the same COUNTIFS approach with date values or cell references: =COUNTIFS(A2:A100,">="&DATE(2025,1,1),A2:A100,"<="&DATE(2025,12,31))
How do I count between two numbers in Google Sheets? The same COUNTIFS formula works in Google Sheets. The syntax is identical, so you can copy the formula directly.
What if I want to count cells that are not between two numbers? Use =COUNTIFS(range,"<"&low,range,">"&high). This counts values less than the lower bound or greater than the upper bound, effectively the complement.
Does COUNTIFS work with whole columns like A:A? Yes, but it may slow down large workbooks because it scans the entire column. It is better to limit the range to the actual data area.
Can I use COUNTIFS with multiple ranges and criteria? Absolutely. For example, to count rows where column A is between 10 and 20 and column B equals “Yes”, use =COUNTIFS(A2:A100,">=10",A2:A100,"<=20",B2:B100,"Yes").
Related
- SPLIT Function in Google Sheets: How to Use It
- Shortcut Keys Save As: The Full Guide
- Is SELECT a Clause in SQL? Yes, Here’s Why
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- LEN Function in Excel: Count Characters in CellsIn Excel, use the LEN function to count the characters in a cell: with text in A2, enter…
- String Function: Meaning, Examples, and Excel UsesA string function is a function that works with text made up of characters: it might count, extract,…
- Excel Formula to Check Whether a Value Is in a ListWith list items in A2:A10 and the value to check in D2, use =IF(COUNTIF($A$2:$A$10,D2)>0,"Found","Not found"). COUNTIF checks whether…
- C String Functions: How They Work and When to Use ThemC string functions are standard library routines declared in <string.h> for measuring, copying, comparing, joining, and searching strings.…
- Databricks SQL SPLIT Function: Syntax and ExamplesIn Databricks SQL, split(str, regex [, limit]) divides a string wherever it matches a regular expression and returns…
- How to Concatenate Names in Excel With a Space EasilyTo concatenate first and last names in Excel with a space, put the first name in A2 and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.