COUNTIF Between Two Values in Excel (Step-by-Step)

Formulas & functions · Updated November 22, 2026

The simplest way to count cells between two values in Excel is to use the COUNTIFS function with two criteria: =COUNTIFS(range,">=lower",range,"<=upper"). For example, =COUNTIFS(B2:B20,">=10",B2:B20,"<=20") counts every number in B2:B20 that is 10 or greater and 20 or less. If you only need to count values strictly greater than one number and strictly less than another, just drop the equals signs: =COUNTIFS(range,">lower",range,".

Why COUNTIF Won't Do It Alone

The older COUNTIF function handles only a single condition, so it cannot directly count a range between two values in one call. Some people try =COUNTIF(range,">=10")-COUNTIF(range,">20"), which works, but it is longer and easier to mis-type. The modern COUNTIFS function (available in all Excel versions from 2007 onward) accepts multiple criteria and is the cleanest solution. If you are using Excel 2019 or earlier, COUNTIFS is still fully supported, so there is no need to upgrade just for this.

Exact Syntax and How It Works

Assume your numbers are in cells A2:A100. To count values between 5 and 15 inclusive, enter this in any empty cell:

=COUNTIFS(A2:A100,">=5",A2:A100,"<=15")

Excel evaluates each cell in A2:A100 against both criteria. A cell is counted only if it satisfies the first condition (>=5) AND the second (<=15). The result is a single number. For example, if the range contains 4, 5, 7, 15, 16, the formula returns 3 (5, 7, and 15).

To count values strictly greater than 5 and strictly less than 15, use:

=COUNTIFS(A2:A100,">5",A2:A100,"<15")

That same example returns 1 (only 7).

Using Cell References for the Bounds

Instead of typing the numbers directly into the formula, you can put the lower bound in a cell (say D1) and the upper bound in D2, then reference them:

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

The ampersand (&) joins the comparison operator with the cell value. This makes the formula easy to update without editing the formula itself. Remember to use double quotes around the operator and the ampersand outside the quotes. If you type ">=D1" instead, Excel will treat it as text and return zero.

Counting Between Dates or Times

The same COUNTIFS pattern works for dates and times, because Excel stores them as numbers. Suppose you have dates in C2:C50 and you want to count how many fall in January 2025. Put the start date in E1 and end date in E2, then use:

=COUNTIFS(C2:C50,">="&E1,C2:C50,"<="&E2)

Make sure E1 and E2 contain real date values, not text. If you type 01/01/2025 and Excel does not recognize it as a date, the formula will return 0. To avoid that, use the DATE function: =DATE(2025,1,1) in E1 and =DATE(2025,1,31) in E2.

Common Mistakes and How to Fix Them

  • Using COUNTIF instead of COUNTIFS: COUNTIF only accepts one range/criteria pair, so it cannot handle two bounds. Switch to COUNTIFS.
  • Reversing the criteria order: Excel does not care about order, but if you accidentally use "<="&lower and ">="&upper, you will get zero because no number can be both less than or equal to 5 and greater than or equal to 15.
  • Missing the ampersand: When referencing cells, you must concatenate with &. Writing ">=D1" treats the whole thing as text, not a comparison.
  • Counting text that looks like numbers: If your range contains numbers stored as text, COUNTIFS will ignore them. Use the VALUE function or Text to Columns to convert them.
  • Including blank cells: Blank cells are not counted because they do not meet numeric criteria. If you want to count blanks separately, use COUNTBLANK.

Alternatives: SUMPRODUCT and Dynamic Arrays

If you are using Excel 365 or Excel 2021, you can use the FILTER function to return the actual values between two bounds, then count them with COUNTA:

=COUNTA(FILTER(A2:A100,(A2:A100>=5)*(A2:A100<=15)))

This returns the same count, but it also lets you see which values matched if you remove the COUNTA. Another classic alternative is SUMPRODUCT:

=SUMPRODUCT((A2:A100>=5)*(A2:A100<=15))

This works in any Excel version, but it is less intuitive and slightly slower on very large ranges. For most users, COUNTIFS is the best choice because it is purpose-built, fast, and easy to read.

Frequently Asked Questions

How do I count cells between two values but exclude the endpoints?

Use strict inequality operators without the equals sign: =COUNTIFS(A2:A100,">5",A2:A100,"<15"). This counts only numbers greater than 5 and less than 15, so 5 and 15 themselves are excluded.

Can I count between two values in Google Sheets the same way?

Yes. Google Sheets supports COUNTIFS with the same syntax. The only difference is that in Google Sheets you might see a comma instead of a semicolon depending on your locale, but the function name and logic are identical. Excel and Google Sheets both handle the ampersand concatenation the same way.

Why does my COUNTIFS return 0 even though I have matching numbers?

Check three things: (1) the range references are correct, (2) the criteria are typed as text with quotes, and (3) the numbers are not stored as text. If a cell contains a number with a leading apostrophe or a space, COUNTIFS will ignore it. Use the ISNUMBER function to test a cell, or select the column and use Text to Columns to convert.

What if I want to count between two values across multiple columns?

COUNTIFS can handle multiple ranges, but each range must be the same size. For example, to count values between 5 and 15 in columns A and B, use =COUNTIFS(A2:A100,">=5",A2:A100,"<=15")+COUNTIFS(B2:B100,">=5",B2:B100,"<=15"). There is no single formula that counts across non-contiguous ranges without adding them.

Can I use COUNTIFS with wildcards for text between two values?

No. COUNTIFS works with text wildcards like * and ?, but for numeric between conditions you must use comparison operators. If you have text that represents numbers, convert it to numeric first.

Is there a way to count between two values using a pivot table?

Yes, but it is more complex. You can group numeric fields in a pivot table by right-clicking the field and choosing Group, then set the starting and ending values and the step. The pivot table will show counts per group, but it will not give a single count for an arbitrary range without extra steps. For a quick one-off count, COUNTIFS is simpler.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.