Excel COUNTIF for a Number Range: Formula and Examples

Formulas & functions · Updated November 19, 2026

To count numbers between two limits in Excel, use COUNTIFS, which applies both a lower and an upper condition to the same range. For example, with values in A2:A100 and the limits 10 and 20, use =COUNTIFS(A2:A100,">=10",A2:A100,"<=20") to count values from 10 through 20, including both endpoints.

Why counting a range needs two conditions

COUNTIF counts cells that meet one condition: for example, values greater than or equal to 10. A number range such as 10 through 20 has two conditions at once: each counted value must be at least 10 and no more than 20. Excel’s COUNTIFS function is designed for this kind of AND test. It counts a row only when all of its paired criteria are met.

The function names are easy to mix up. COUNTIF takes one range and one criterion; COUNTIFS can evaluate multiple range-and-criterion pairs. Both are available in current desktop Excel for Microsoft 365 on Windows, as well as many earlier Excel versions. For a simple interval, you do not need a special add-in or a separate analysis tool.

A number interval can be inclusive or exclusive at either end. “10 to 20” commonly means that 10 and 20 count, while “above 10 but below 20” excludes both. Decide what the wording means before writing the criteria, because changing >= to > changes the result for values exactly on the boundary.

Build the basic COUNTIFS formula

Use the same data range twice: once to test the lower limit and once to test the upper limit. The criteria are text strings containing a comparison operator and a number. This is the general pattern:

With the numbers to evaluate in A2:A100, this formula counts values greater than or equal to 10 and less than or equal to 20: =COUNTIFS(A2:A100,">=10",A2:A100,"<=20"). Excel returns the number of cells in A2:A100 that pass both tests.

For instance, suppose A2:A11 contains 4, 8, 10, 12, 15, 18, 20, 21, 25, and 30. With those values in A2:A11, =COUNTIFS(A2:A11,">=10",A2:A11,"<=20") returns 5: the qualifying values are 10, 12, 15, 18, and 20. The values 8 and 21 are outside the interval, and the other values are farther outside it.

The paired ranges in COUNTIFS need to have matching dimensions. Here, both criteria refer to A2:A11, so Excel checks the two conditions against each cell in that range. When you count a single column, repeat that column range for each condition. If you are counting rows based on different columns, each criteria range should cover the corresponding rows.

Use cells for limits that may change

Hard-coded bounds are convenient for a one-off calculation, but worksheet cells make the count easier to update. Put the lower limit in D2 and the upper limit in D3. If the values being counted are in A2:A100, the formula is:

With values in A2:A100, the lower bound in D2, and the upper bound in D3, use =COUNTIFS(A2:A100,">="&D2,A2:A100,"<="&D3). If D2 is 10 and D3 is 20, it returns 5 for the example data listed above.

The ampersand joins an operator such as >= to the value stored in a cell. Excel needs this concatenation because the criterion must contain both the comparison operator and the bound. A common mistake is writing ">=D2", which asks Excel to compare against text that includes the characters D2 rather than using the value in D2.

If you plan to copy the formula to other rows or columns, consider locking the data range and limit cells with dollar signs. For example, if the formula will be filled down, absolute references keep the bounds from shifting. With data in A2:A100 and bounds in D2 and D3, a copy-down version is =COUNTIFS($A$2:$A$100,">="&$D$2,$A$2:$A$100,"<="&$D$3). The dollar signs do not change the result; they control how references adjust when a formula is copied.

Choose inclusive or exclusive endpoints

Use >= for “at least” and <= for “at most.” Use > for “greater than” and < for “less than.” For example, a count from 10 through 20 includes both boundary values, while a count strictly between 10 and 20 excludes them.

With numbers in A2:A11, a strict count above 10 and below 20 is =COUNTIFS(A2:A11,">10",A2:A11,"<20"). For the example values, the result is 3: 12, 15, and 18 qualify. Exactly 10 and exactly 20 do not.

When the lower and upper limits are the same, an inclusive interval counts values equal to that number. If the lower limit is greater than the upper limit, the criteria cannot both be true for an ordinary numeric value, so the result will be zero. If users enter bounds themselves, you may want to check their order in a separate cell or explain the expected order beside the inputs.

Count values in a date range

Excel stores real dates as serial numbers, so the same two-condition approach works for dates. The important detail is to use actual Excel dates in the data and in the bound cells, not date-looking text. Cell formatting controls how a date appears, but the underlying value must be a date Excel recognizes.

For a date range that includes both the start date and the end date, put transaction dates in A2:A100, the start date in D2, and the end date in D3. Use =COUNTIFS(A2:A100,">="&D2,A2:A100,"<="&D3). This counts dates from the start through the end, inclusive, assuming the cells contain dates without time-of-day values.

If the source contains date-and-time values, a record on the final date may have a time later than midnight and therefore be greater than the end date stored in D3. A reliable pattern for including the whole final calendar day is to set D3 to the day after the desired end date and use a strict less-than condition for that upper bound. For dates and times in A2:A100, with the start date in D2 and the day after the desired final date in D3, use =COUNTIFS(A2:A100,">="&D2,A2:A100,"<"&D3). This includes all times on the start date and subsequent dates before D3, without including midnight at the next day.

Common reasons the count looks wrong

When a result is unexpectedly low or high, check the values and criteria before rebuilding the formula. These are frequent sources of confusion:

  • Numbers stored as text: A cell may look numeric but contain text, often after an import or when an apostrophe was entered first. Convert the data to numbers if it should be numeric, then recalculate the count.
  • Wrong boundary operator: A formula using > excludes a value equal to the lower bound. Confirm whether the requested interval includes its endpoints.
  • Criteria entered as plain text: When a limit is in a cell, join it to the operator with &. Do not place the cell address inside a quoted criterion.
  • Different range sizes: Keep the criteria ranges aligned and the same size. Repeating A2:A100 for both tests avoids mismatched ranges when testing one column.
  • Unexpected spaces or imported content: Text entries, blanks, and errors are not ordinary numeric values. Inspect the source cells, especially when data came from a website, CSV, or another system.
  • Dates with time values: An end-date condition of less than or equal to midnight can miss later times on that date. Use the next day as an exclusive upper limit when the full final day should count.

To inspect the source, select a suspicious cell and look at the formula bar; changing the number format alone does not necessarily convert text into a number. You can also test a small, known subset manually. For a large list, filtering the data to the same two bounds can help reveal which records Excel is counting, though the formula remains the more repeatable calculation.

When COUNTIF alone or another method is useful

If you only have one boundary, COUNTIF is enough. With values in A2:A100, =COUNTIF(A2:A100,">=10") counts numbers at least 10; it does not impose an upper limit. This is useful for questions such as “How many scores passed?” when only one threshold defines the answer.

You can also calculate an interval by subtracting cumulative counts, but it is usually less readable than COUNTIFS. For example, with values in A2:A100, =COUNTIF(A2:A100,">=10")-COUNTIF(A2:A100,">20") counts values from 10 through 20 inclusive: all values at least 10, minus those greater than 20. Prefer COUNTIFS when expressing both limits directly makes the intended interval clearer.

For more involved conditions, such as counting rows that meet a number interval and also match a category, add another range-and-criterion pair to COUNTIFS. With amounts in B2:B100 and category labels in C2:C100, and bounds 10 and 20, this formula counts amounts in the interval only where the category is exactly “Books”: =COUNTIFS(B2:B100,">=10",B2:B100,"<=20",C2:C100,"Books"). The result depends on the worksheet data; each row must meet all three conditions.

Frequently Asked Questions

Can COUNTIF check whether a number is between two values?
Not directly with one COUNTIF criterion: it applies one condition. Use COUNTIFS with the same data range twice, once for the lower limit and once for the upper limit.

How do I count numbers between two cells?
Put the lower and upper limits in separate cells and concatenate each cell reference with its comparison operator. For example, with values in A2:A100 and bounds in D2 and D3, use =COUNTIFS(A2:A100,">="&D2,A2:A100,"<="&D3).

Does COUNTIFS include the minimum and maximum?
It includes them only when you use inclusive operators: >= for the minimum and <= for the maximum. Replace either operator with > or < to exclude that endpoint.

Can I count decimals within a range?
Yes. Excel compares numeric values, including decimals, using the same criteria. For example, an inclusive interval from 1.5 through 2.5 includes 1.5, 2, and 2.5, as well as any other numeric values between them.

Why does my formula return zero when numbers are visible?
Check whether the entries are stored as text, whether the criteria use the intended operators, and whether the bounds are in the right order. A number format can make text look like a number without changing its stored type.

Does this work in Excel for Mac and Excel for the web?
Yes. COUNTIF and COUNTIFS are available in current Excel for Mac and Excel for the web as well as Excel for Microsoft 365 on Windows. The formula logic is the same; interface details can vary, and regional settings may use a different argument separator.

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.