COUNTIF in Excel Between Two Values: The Right Formula

Formulas & functions · Updated November 18, 2026

To count numbers between two limits in Excel, use COUNTIFS, which applies both limits to the same range. For values in A2:A100 and lower and upper bounds in D2 and E2, enter =COUNTIFS(A2:A100,">="&D2,A2:A100,"<="&E2) to count values from the lower limit through the upper limit, including both.

Why counting between values needs COUNTIFS

COUNTIF tests a range against one condition, such as “at least 10” or “no more than 18.” A range between two limits has two conditions: it must be at least the lower bound and no more than the upper bound. COUNTIFS is designed to apply multiple conditions together, so it is the straightforward choice for this task in Microsoft Excel for Microsoft 365 on Windows.

Both conditions in the formula must be true for a value to be counted. In the example, the first range-and-criteria pair checks whether a value is greater than or equal to D2. The second pair checks whether it is less than or equal to E2. The repeated range means Excel checks both tests against each value in A2:A100, rather than counting values from two separate lists.

The function name is plural because it accepts multiple criteria. Although people often search for “COUNTIF between,” using two separate COUNTIF formulas is not necessary for the usual inclusive range check. One COUNTIFS formula expresses the rule directly and is easier to update when the limits change.

Count values including both limits

For a concrete example, assume A2:A8 contains 4, 8, 10, 12, 15, 18, and 22, while D2 contains 10 and E2 contains 18. With those values in place, enter this formula in any empty cell:

With the data in A2:A8 and the lower and upper bounds in D2 and E2, respectively:

=COUNTIFS(A2:A8,">="&D2,A2:A8,"<="&E2)

The result is 4: Excel counts 10, 12, 15, and 18. The values 4 and 8 fail the lower-bound test, while 22 fails the upper-bound test. Since the operators are >= and <=, values exactly equal to either limit are included.

If you prefer to put the limits directly in the formula rather than use cells, the same example can be written with criteria constants. With the example numbers in A2:A8 and limits of 10 and 18:

=COUNTIFS(A2:A8,">=10",A2:A8,"<=18")

This also returns 4. Cell references are generally more convenient in a working spreadsheet: changing D2 or E2 updates the count without editing the formula. Direct criteria can be useful for a quick check or a small, fixed calculation.

Exclude the lower or upper limit

“Between” does not always mean that the endpoints count. If the requirement is strictly greater than the lower limit and strictly less than the upper limit, use > and < instead. With values in A2:A8, a lower limit of 10 in D2, and an upper limit of 18 in E2:

=COUNTIFS(A2:A8,">"&D2,A2:A8,"<"&E2)

The result is 2 because only 12 and 15 are strictly inside the limits. To include the lower endpoint but exclude the upper, use >= for the first condition and < for the second. To exclude the lower endpoint but include the upper, use > and <=. Choosing these operators deliberately prevents off-by-one errors in counts of test scores, prices, ages, and other measurements.

For whole numbers, the distinction may seem minor, but it matters for decimal measurements too. If the limits are 10 and 18, a value such as 10.5 is inside either interpretation, while 10 itself is counted only when the lower condition includes equality. Check the wording of the business rule or assignment rather than assuming endpoints should always count.

Use cells for changeable limits

Putting bounds in cells makes a formula easier to reuse. Label D1 “Minimum” and E1 “Maximum,” enter the values in D2 and E2, and keep the observations in A2:A100. Then use the inclusive formula from the opening paragraph. You can change either limit without changing the formula, which is useful for interactive summaries, reports, and repeated checks.

Excel criteria that combine an operator with a cell reference need the ampersand operator. For example, the criterion is built from the text ">=" followed by the value in D2. Do not put the cell reference inside the quotation marks: ">=D2" would be treated as literal criterion text, not as a comparison to the value stored in D2. The same principle applies to the upper-bound criterion.

Make sure the lower limit is not greater than the upper limit. If D2 is 18 and E2 is 10, no ordinary number can satisfy both conditions, so the result is zero. Excel does not automatically swap the bounds. You can check or correct the input cells, or add a clear note to the sheet explaining which cell is the minimum and which is the maximum.

Count dates and date-times correctly

Excel stores dates as serial numbers, so COUNTIFS can compare dates using the same greater-than and less-than operators. The date cells and the limit cells should contain actual Excel dates, not text that merely looks like a date. Apply a date format to display them clearly; formatting changes how a value appears, not the underlying comparison.

For date-only entries, an inclusive start and end date can use the usual >= and <= conditions. A subtle issue arises when the data also contains times. A cell displaying the end date may contain a time later than midnight, so a condition of “less than or equal to the end date” can omit records from later that day. To include every time on the end date, make the upper test less than the following day.

With date-times in A2:A100, the start date in D2, and the final date in E2, where D2 and E2 are date values without times:

=COUNTIFS(A2:A100,">="&D2,A2:A100,"<"&(E2+1))

This counts timestamps from midnight at the start date up to, but not including, midnight after the end date. For example, if E2 is a Monday, a record at 11:30 p.m. on Monday is included, while a record at midnight Tuesday is not. The formula assumes the date-time values are real Excel values and that E2 is a date without a time component.

What the formula counts in mixed data

For a numeric range, COUNTIFS counts numeric values that meet both criteria. Text labels such as “N/A” are not numbers between numeric bounds and will not contribute to this count. Blank cells also do not represent values inside a normal positive numeric interval. If the cells contain numbers stored as text, they may not behave like numeric values in comparisons, even when they look identical on screen.

To check for numbers stored as text, select a few suspicious cells and look at the formula bar or try converting a copy of the data using a suitable method such as Text to Columns. Do not convert blindly if the column contains identifiers: a code like 00125 may need to remain text because its leading zeros are meaningful. For numeric analysis, make sure the source column is genuinely numeric before relying on the count.

Also keep the criteria ranges the same size. In the example, both criteria ranges are A2:A100. COUNTIFS is intended to pair corresponding cells across the criteria ranges; using mismatched ranges can produce an error or an unintended setup. Whole-column references such as A:A can be convenient, but finite ranges are often easier to audit and can reduce unnecessary calculation in a large workbook.

Common errors and how to fix them

A result of zero does not always mean there are no qualifying observations. Check the following points before rebuilding the formula:

  • Wrong endpoint operators: Confirm whether the rule includes the endpoints. Use >= and <= for an inclusive interval, or strict operators when endpoints must be excluded.
  • Bounds reversed: Verify that the minimum is no greater than the maximum. The formula does not rearrange limits for you.
  • Text instead of numbers: Values imported from a CSV or copied from a website may be stored as text. Convert genuine numeric data to numbers, while preserving text identifiers where appropriate.
  • Criteria typed incorrectly: When referring to a bound cell, join the operator and the reference with &. Keep the comparison operator in quotation marks, but the cell reference outside them.
  • Dates include times: If the final day is missing from the count, use a less-than test against the day after the end date, as shown above.
  • Ranges do not match: Check that each criteria range covers the intended records and has the same dimensions as the other criteria ranges.

If the formula shows an error rather than a number, check for missing commas, quotation marks, or parentheses. Excel for Microsoft 365 on Windows normally uses commas between function arguments under standard regional settings. Some regional settings use semicolons instead; if Excel rejects a correctly structured formula with commas, use the list separator configured for that installation. The formula logic remains the same.

COUNTIF, COUNTIFS, and other options

Use COUNTIF when you have one condition, such as counting all values above a threshold. Use COUNTIFS when a count must meet two or more conditions at once, including a lower and upper limit. A common misconception is that a single COUNTIF can express a range between two values; its syntax accepts one range-and-criterion pair, so the two-bound case is more naturally handled by COUNTIFS.

For a one-off manual check, you could filter the data and inspect the visible records, but a formula is more useful when the count needs to update automatically. Excel’s filter controls are available from the Data tab, and filtering does not replace a formula when you need a count displayed in a report or used in another calculation. For most worksheets, COUNTIFS is clearer than a more elaborate array calculation because its criteria are visible and easy to edit.

If the task is to return the matching values rather than count them, a count formula is not the right output. Microsoft 365 includes the FILTER function, which can return matching rows, but it serves a different purpose. Use a count when the question is “how many?” and a filtering or extraction approach when the question is “which records?”

Frequently Asked Questions

Can COUNTIF count between two numbers?

Not with one criterion pair. Use COUNTIFS to test the same range against both a lower and an upper limit. It is the direct way to count values between two numbers, with the choice of inclusive or exclusive operators depending on the rule.

Does between include the two limits in Excel?

It depends on the formula. The operators >= and <= include values equal to the lower and upper limits. The operators > and < exclude them. Choose the version that matches what “between” means in your data or instructions.

How do I use numbers in cells as the limits?

Put the lower and upper values in separate cells, then join each comparison operator to its cell reference with &. For example, use a lower-bound cell for the ">="& criterion and an upper-bound cell for the "<="& criterion. This lets the count update when either limit changes.

Why does my between formula return zero?

Check whether the values are numbers rather than text, whether the two limits are in the intended order, and whether the formula’s ranges cover the data. Then confirm that the formula uses the correct endpoint operators and that criteria built from cell references use an ampersand. For date-time records, check whether the end-date test excludes times later that day.

How do I count dates through the end date when cells contain times?

Use a start-date condition that is greater than or equal to the first date, and an end condition that is less than the day after the final date. This includes all times on the final date without including midnight at the start of the following day. Ensure the date limits are real Excel dates.

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.