COUNTIF in Excel: Count Between Two Numbers

Formulas & functions · Updated November 18, 2026

To count numbers between two limits in Excel, use COUNTIFS with the same range twice: =COUNTIFS(A2:A100,">=10",A2:A100,"<=20"). This counts values from 10 through 20, including both endpoints; use > or < instead when a boundary should be excluded.

Why COUNTIFS Is the Right Function

Although the question is often phrased as “How do I use COUNTIF between two numbers?”, the usual solution uses COUNTIFS, with an s. COUNTIF evaluates one condition, while COUNTIFS evaluates multiple conditions together. To define a range, Excel needs to check that each value meets both a lower-limit condition and an upper-limit condition.

For example, with scores in A2:A100, the lower test asks whether a score is at least 10, and the upper test asks whether it is at most 20. A row is counted only when both tests are true. In Microsoft 365 for Windows, enter the formula in an empty cell and press Enter. The formula works in current Excel for Mac and Excel for the web as well; ribbon details are not needed for this calculation.

The range must be numeric for a numeric count. Cells containing text that looks like a number may not behave like actual numbers, depending on how the text was entered or imported. If the result seems wrong, check the underlying cell values rather than relying only on how they look in the worksheet.

Inclusive and Exclusive Limits

The comparison operators determine whether values exactly equal to either limit are included. The most common interpretation of “between 10 and 20” is inclusive, meaning 10 and 20 both count. In a formula, >= means greater than or equal to, and <= means less than or equal to.

Assume the values 8, 10, 12, 15, 20, 21, 10, 18, and 25 are in A2:A10. The inclusive formula returns 6: the qualifying values are 10, 12, 15, 20, 10, and 18. If the requirement is strictly greater than 10 and strictly less than 20, the result is 3: 12, 15, and 18.

With the same values in A2:A10, this formula excludes both endpoints: =COUNTIFS(A2:A10,">10",A2:A10,"<20"). To include the lower endpoint but exclude the upper one, use >= for the lower test and < for the upper test. Choosing the operators deliberately prevents common off-by-one-boundary errors.

Build a Formula Using Cell Limits

Hard-coding limits is convenient for a one-time count, but storing them in cells makes the calculation easier to reuse. Put the minimum in C1 and the maximum in D1, then refer to those cells in the criteria. For values in A2:A100, with the limits in C1 and D1, use =COUNTIFS(A2:A100,">="&C1,A2:A100,"<="&D1).

Excel criteria that combine an operator with a cell reference need concatenation: the operator is written as text, then joined to the cell value with the ampersand. The formula reads the current values in C1 and D1, so changing either limit updates the count. This is useful for a small report where a reader needs to adjust a threshold without editing the formula.

Keep the criteria range and its paired criteria range the same size. Here both refer to A2:A100. In more complex summaries, COUNTIFS can check different ranges at the same time—for example, one range for a score and another for a department—but the ranges must align in dimensions. If the lower limit is greater than the upper limit, no ordinary number can satisfy both tests, and the result will normally be zero.

Use Named Cells or a Table for Reusable Reports

Cell references are often enough, but named cells can make a formula easier to read. If C1 is named Minimum and D1 is named Maximum, the same range logic can refer to those names instead. Names are especially helpful when limits appear on a separate settings sheet, although a clear label beside each limit is still useful to anyone maintaining the workbook.

Excel Tables are another option for data that grows over time. If a table named Sales has a column named Amount, a formula can use that column as its range rather than a fixed address. With Amount values in the Sales table and limits in C1 and D1, use =COUNTIFS(Sales[Amount],">="&C1,Sales[Amount],"<="&D1). When rows are added to the table, the structured reference expands with the column.

Use a table when new records are routinely appended and a fixed range could omit them. For a small, static list, a normal cell range may be simpler. In either case, make sure the criteria refer to the intended data column; including a header row in a numeric range is usually unnecessary and can make a formula harder to inspect.

Count Numbers Between Dates or Times

Excel stores real dates as serial numbers, so COUNTIFS can apply the same two-boundary method to a date column. Enter actual Excel dates in the cells used for the lower and upper limits, rather than date-looking text. With dates in A2:A100, the start date in C1, and the end date in D1, use =COUNTIFS(A2:A100,">="&C1,A2:A100,"<="&D1). This includes records on both boundary dates when the cells contain dates without times.

A date-time value also contains a fractional part representing the time of day. Consequently, a record on the end date at noon is greater than the serial value for that date at midnight and would fail an inclusive upper test against D1. To include every time on the end date, set the upper test to less than the day after D1. With date-times in A2:A100 and a start date in C1 and end date in D1, use =COUNTIFS(A2:A100,">="&C1,A2:A100,"<"&(D1+1)).

This end-exclusive approach is a dependable way to include the full final calendar day without guessing the last possible time value. For time-only data, confirm that the stored values are genuine Excel times and decide whether the end time itself should count. The same operator choice used for numeric limits applies to date and time values.

Common Problems and How to Fix Them

A zero or unexpectedly low count often comes from a mismatch between the displayed data and its stored type. Imported numbers may be text, a date may be text rather than an Excel date, or a cell may contain a hidden space. Formatting a text value to display as a number does not necessarily convert the stored value. Use Excel’s error indicators, test a sample cell, or convert the source data before relying on a numeric count.

Check these points if a COUNTIFS result does not match a manual count:

  • Confirm both comparison operators and whether each endpoint should be included.
  • Verify that the lower limit is not greater than the upper limit.
  • Make sure the data range covers all records and does not accidentally include unrelated values.
  • Check that the limit cells contain numbers or real dates, not text that only looks like them.
  • Ensure each range in COUNTIFS has the same number of rows and columns.
  • Look for date-time values when the intended boundary is a calendar date.

Another common issue is typing a criterion as though it were a normal comparison expression inside quotation marks. A fixed criterion such as ">=10" is valid because Excel receives the operator and number as a criterion string. When the number comes from a cell, join the operator text to the cell reference with &; putting the cell reference inside quotation marks makes it literal text instead of a reference.

COUNTIF, COUNTIFS, and Other Approaches

COUNTIF is useful when there is just one condition, such as counting numbers greater than 10. It cannot express the ordinary two-sided range in a single criterion because both the lower and upper comparisons must be applied. COUNTIFS is the clearest standard choice for that task and also scales naturally when a count needs additional conditions, such as counting values within a range for one particular category.

It is possible to construct other formulas, but they are not usually easier to maintain. For example, one could count values below an upper boundary and subtract values below the lower boundary, adjusting the operators carefully for inclusive or exclusive endpoints. That approach is more error-prone around equality and can be less readable than two criteria in COUNTIFS. A filter can help inspect matching rows, but it is a manual viewing method rather than a simple formula that returns a count.

For most worksheets, choose COUNTIFS when the question is “how many values meet both limits?” Choose COUNTIF for one criterion, and use a filter when the goal is to review or edit the matching records. If your Excel uses a regional setting that expects semicolons between function arguments, replace the commas in examples with the separator your installation requires; the comparison logic stays the same.

Frequently Asked Questions

Can I use COUNTIF to count between two numbers?
Not as a straightforward single-criterion formula. Use COUNTIFS to apply both the lower and upper comparisons to the same range. It is the simplest way to count values meeting both limits.

Does “between” include the two numbers?
It depends on the requirement. Use greater-than-or-equal and less-than-or-equal operators to include both boundaries. Use strict greater-than or less-than operators wherever the boundary value should not count.

How do I count values between limits stored in cells?
Join each comparison operator to its limit cell with an ampersand. For example, with values in A2:A100 and the minimum and maximum in C1 and D1, respectively, use =COUNTIFS(A2:A100,">="&C1,A2:A100,"<="&D1).

Can COUNTIFS count a range of dates?
Yes. Use the date column for both criteria and use actual Excel date values for the limits. If the data contains times and the final calendar date must be included in full, make the upper boundary exclusive of the following day.

Why does my formula return zero?
Check for text-formatted numbers or dates, incorrect boundary operators, mismatched ranges, and limits in the wrong order. Also verify that the data range includes the rows you expect and that the cell-based criteria are joined to their operators correctly.

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.