Excel Formula Between Two Numbers: Examples

Formulas & functions · Updated November 18, 2026

To check whether a value in A2 is between a lower limit in B2 and an upper limit in C2, use =AND(A2>=B2,A2<=C2). This returns TRUE when A2 is within the limits, including either endpoint, and FALSE otherwise.

Excel does not have a dedicated BETWEEN operator, so the usual approach is to combine two comparisons with AND. The right version depends on whether the limits count as “between,” whether you want a label or a count instead of TRUE or FALSE, and how your data is arranged.

What “between” means in Excel

In everyday language, “between 10 and 20” can mean either that 10 and 20 are included or that only values strictly inside those limits count. Excel formulas make that choice explicit. Use greater-than-or-equal-to and less-than-or-equal-to for an inclusive range; use the strict greater-than and less-than operators for an exclusive range.

The AND function is useful because a value is between two limits only when both statements are true: it is at least the lower limit, and it is no greater than the upper limit. Excel returns the logical results TRUE or FALSE. Those results can be displayed directly, used by IF, or used to filter, count, or format data.

For example, if the limits are 10 and 20, the inclusive test accepts 10, 15, and 20. The exclusive test accepts 15 but not 10 or 20. Deciding which interpretation fits your task is important for things such as grading cutoffs, date windows, quality-control thresholds, and eligibility rules.

Test whether one value is within inclusive limits

Suppose A2 contains the value to check, B2 contains the lower limit, and C2 contains the upper limit. Enter this formula in D2:

=AND(A2>=B2,A2<=C2)

If A2 is 15, B2 is 10, and C2 is 20, the result is TRUE. If A2 is 21, the result is FALSE. Because the formula refers to cells, you can change the value or either limit and Excel recalculates the check. Copy the formula down column D to test additional rows, provided each row has its value and limits in the same columns.

The formula can also use fixed limits typed directly into it. With the value to check in A2 and fixed limits of 10 and 20, enter:

=AND(A2>=10,A2<=20)

Cell references are usually easier to maintain when limits may change: update the limit cells once rather than editing multiple formulas. If you plan to copy a formula while keeping the limits in the same cells, use absolute references. With the value in A2 and fixed limits in B2 and C2, enter:

=AND(A2>=$B$2,A2<=$C$2)

The dollar signs keep the references to B2 and C2 fixed when you fill the formula down. A2 remains relative, so it changes to A3, A4, and so on. In Excel for Microsoft 365 on Windows, F4 cycles through reference styles while you edit a cell reference; on a Mac, the equivalent shortcut is usually Command+T, though keyboard settings can affect function-key behavior.

Use exclusive limits when endpoints do not count

For a test that excludes both endpoints, use greater-than and less-than rather than their inclusive versions. With the value in A2, lower limit in B2, and upper limit in C2, enter:

=AND(A2>B2,A2<C2)

With limits of 10 and 20, a value of 15 returns TRUE, while 10 and 20 both return FALSE. This distinction can matter when neighboring ranges share a boundary. For example, if one band is intended to cover values up to and including 20 and the next starts above 20, an inclusive upper limit in the first band and a strict lower limit in the second avoid counting 20 in both.

For dates and times, Excel stores valid dates as serial numbers and times as fractions of a day, so the same comparison operators work. If a reporting period should include every time on its final date, be careful: a limit entered as a date alone represents the start of that date. The formula may exclude later times that day. A common approach is to define the upper limit as the next day and use a strict less-than comparison, but choose the boundary according to how the source data records dates and times.

Show a word instead of TRUE or FALSE

A logical result is convenient for calculations, but a status label may be clearer in a report. With the value in A2, lower limit in B2, and upper limit in C2, enter this formula in D2:

=IF(AND(A2>=B2,A2<=C2),"Between","Outside")

If A2 is 15 and the limits are 10 and 20, the formula returns Between. If A2 is 25, it returns Outside. The text labels are customizable; for example, you could use “In range” and “Out of range.” The quoted text must be enclosed in straight double quotation marks in Excel formulas.

For a numeric result rather than a label, IF can return a number on each branch. With the same cell layout, this formula returns 1 when A2 is within the inclusive range and 0 otherwise:

=IF(AND(A2>=B2,A2<=C2),1,0)

That can be useful when another calculation needs a numeric flag. However, if the goal is only to count qualifying records, COUNTIFS is often more direct and easier to audit.

What if the lower and upper limits are reversed?

The basic formula assumes the lower limit is in B2 and the upper limit is in C2. If someone might enter those limits in either order, MIN and MAX can identify the smaller and larger values. With the value in A2 and the two limits in B2 and C2, use:

=AND(A2>=MIN(B2,C2),A2<=MAX(B2,C2))

If B2 is 20, C2 is 10, and A2 is 15, this formula returns TRUE. This approach is useful for user-entered limits or comparisons where order is not meaningful. If the order does matter—for example, an incorrectly entered start date and end date should be flagged rather than silently accepted—keep the basic formula and validate the inputs separately.

MIN and MAX work with numeric values, including valid Excel dates. They do not make text that looks like a number a dependable numeric limit. If a pasted or imported value behaves unexpectedly, check whether Excel recognizes it as a number or date rather than text.

Count or sum values that fall within a range

To count how many entries meet both limits, use COUNTIFS. Suppose the values to test are in A2:A100, the lower limit is in B2, and the upper limit is in C2. Enter:

=COUNTIFS(A2:A100,">="&B2,A2:A100,"<="&C2)

If the list contains 5, 12, 20, 21, and 10, and B2 is 10 while C2 is 20, the result is 3: the qualifying entries are 12, 20, and 10. COUNTIFS applies both criteria to the same range, so each counted value must satisfy both conditions. The criteria are built by joining the comparison operator to the limit with the ampersand.

If the limits may be reversed, use MIN and MAX inside the criteria. With values in A2:A100 and the two limits in B2 and C2, enter:

=COUNTIFS(A2:A100,">="&MIN(B2,C2),A2:A100,"<="&MAX(B2,C2))

You can also sum amounts associated with qualifying values. Suppose the values to test are in A2:A100, their corresponding amounts are in B2:B100, and the limits are in C2 and D2. Enter:

=SUMIFS(B2:B100,A2:A100,">="&C2,A2:A100,"<="&D2)

If the qualifying values are 12, 20, and 10, and the corresponding amounts are 120, 200, and 100, the formula returns 420. The sum range and criteria range must cover corresponding rows; otherwise, the results will not represent the intended records.

Highlight values between two numbers

Conditional formatting can highlight in-range entries without adding a helper column. If the values are in A2:A100 and the limits are in B2 and C2, select A2:A100. On Windows, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter this formula:

=AND($A2>=$B$2,$A2<=$C$2)

Choose a format and confirm the rule. The dollar signs fix the columns and limit cells, while the row number in $A2 is allowed to change for each selected row. That lets the first row’s test adjust as Excel applies the rule down the selection. On Mac, the Conditional Formatting controls are also on the Home tab, though the dialog appearance can differ. In Excel for the web, the commands and rule options may differ slightly from the Windows desktop dialog.

Conditional formatting changes appearance, not the underlying cell value. It is helpful for scanning a list, but it does not replace a formula if you need a result to use in another calculation or export.

Common problems and checks

If a formula gives an unexpected result, check the cell layout and data types before changing the logic. A visually plausible value may be stored as text, particularly after importing data. Comparisons involving text can behave differently from the numeric test you intended. Use Excel’s number format and the formula bar as clues, and verify questionable inputs by converting them to actual numbers or dates.

  • Confirm the endpoints: Decide whether the lower and upper limits should count. Use >= and <= for inclusive limits; use > and < for exclusive limits.
  • Check the order: Make sure the lower limit is actually smaller than the upper limit, or use MIN and MAX if order should not matter.
  • Inspect blank cells: A missing value or limit can make a test misleading. Decide how blanks should be handled instead of assuming they mean zero or an intentional boundary.
  • Check copied references: If fixed limit cells move as you fill a formula down, add dollar signs to those references.
  • Review dates and times: A date-only endpoint may not include later times on that date. Match the formula’s boundary to the precision of the data.
  • Check errors in source cells: An error value in a referenced cell can propagate through the test. Correct or handle the source error before relying on the result.

A useful misconception to avoid is that “between” always includes the endpoints. It does not have one universal meaning in Excel or in every business rule. The formula’s operators—not the word “between”—determine which boundary values qualify.

Frequently Asked Questions

What is the Excel formula to check if a number is between two numbers?
Use AND with one comparison for each limit. For an inclusive check, compare the value with greater-than-or-equal-to for the lower limit and less-than-or-equal-to for the upper limit. The first example above uses A2 for the value and B2 and C2 for the limits.

Does Excel have a BETWEEN function?
No. Excel has no built-in BETWEEN function or BETWEEN operator. AND combined with two comparisons is the standard way to test a value against a lower and upper limit.

How do I include the minimum and maximum values?
Use >= for the minimum and <= for the maximum. Those operators make both endpoints part of the accepted range. Replace them with > and < if the endpoints must be excluded.

How can I count numbers between two limits?
Use COUNTIFS with the same value range twice: once for the lower-limit criterion and once for the upper-limit criterion. The count formula above shows how to join each comparison operator to a limit stored in a cell.

Can the formula check dates between two dates?
Yes. Excel compares valid dates as numeric serial values, so the same AND test works with date cells. For date-and-time data, decide whether the final date should include all times during that day and set the upper boundary accordingly.

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.