AVERAGEIF Excel Between Values: Formula and Examples

Formulas & functions · Updated November 18, 2026

To average numbers that fall between two limits in Excel, use AVERAGEIFS, not AVERAGEIF, because the range needs two criteria: a lower limit and an upper limit. With values in C2:C10 and the limits in E2 and F2, use =AVERAGEIFS(C2:C10,C2:C10,">="&E2,C2:C10,"<="&F2); this includes both endpoints.

Why AVERAGEIFS is the right function

AVERAGEIF averages cells that meet one condition, such as scores greater than or equal to 70. A range “between” two values requires two conditions at once: each number must be at least the minimum and no greater than the maximum. Excel’s AVERAGEIFS function applies multiple criteria, so it is the direct fit.

The similar names can be confusing. AVERAGEIF has the average range first and one criteria range and criterion after it. AVERAGEIFS also starts with the range to average, but then accepts pairs of criteria ranges and criteria. The “S” signals that several conditions can be used. For a two-bound calculation, both criteria ranges can be the same numeric range.

For the formula above, the first criterion checks for values greater than or equal to E2, and the second checks for values less than or equal to F2. A value is averaged only when it satisfies both tests. This is different from an OR condition, which would include values meeting either test.

Build a simple between-values example

Suppose C2:C7 contains 10, 20, 30, 40, 50, and 60. Enter 25 in E2 as the minimum and 50 in F2 as the maximum. The matching values are 30, 40, and 50. Their total is 120, and dividing by three gives an average of 40.

With those values in C2:C7 and the two limits in E2 and F2, the formula is:

=AVERAGEIFS(C2:C7,C2:C7,">="&E2,C2:C7,"<="&F2)

The result is 40. The ampersand joins each comparison operator to the value in its limit cell, creating criteria such as “greater than or equal to 25.” This lets you change either limit without editing the formula. If you use the same range as both the average range and the criteria ranges, keep all three ranges the same size and shape.

Choose inclusive or exclusive limits

The example uses inclusive limits: >= for the minimum and <= for the maximum. Thus, numbers equal to either boundary count. That is usually what people mean by “between,” but it is worth deciding explicitly, especially for grades, prices, age bands, and measurement ranges.

To exclude the endpoints, change the comparisons to greater than and less than. With values in C2:C7 and limits in E2 and F2, this version averages only values strictly inside the limits:

=AVERAGEIFS(C2:C7,C2:C7,">"&E2,C2:C7,"<"&F2)

For a range such as 25 through 50, the result remains 40 in the sample because neither endpoint appears among the values. If the range contained 25 or 50, those values would be included by the inclusive version and excluded by the strict version. A clear note in a report or worksheet can prevent readers from assuming the wrong boundary rule.

Use numbers typed directly in the formula

Cell references make limits easy to change, but fixed thresholds can also be written directly into the criteria. With values in C2:C7 and a desired range from 25 through 50, use:

=AVERAGEIFS(C2:C7,C2:C7,">=25",C2:C7,"<=50")

The result is 40 for the sample data. Criteria that combine an operator with a fixed number are text strings, which is why the comparison is enclosed in quotation marks. When a criterion uses a cell reference, join the quoted operator and the reference with &, as in the earlier formula. A common mistake is to write the operator and cell address together inside quotes; Excel then treats the address as literal text instead of using the cell’s value.

Apply the calculation to dates

Excel stores valid dates as serial numbers, so AVERAGEIFS can apply numeric comparisons to dates too. If dates are in A2:A20 and the dates to average are in B2:B20, you can use the date column twice as the criteria range. For example, with start and end dates in E2 and F2, the formula is:

=AVERAGEIFS(B2:B20,A2:A20,">="&E2,A2:A20,"<="&F2)

This returns the average of the B values for rows whose dates fall within the inclusive date range. Format E2 and F2 as dates so the limits are readable, and make sure the entries in A2:A20 are real Excel dates rather than text that only looks like dates.

A subtle issue arises when the date column contains times as well as dates. A value on the final day at 3:00 p.m. is later than that day’s midnight, so a condition that is less than or equal to the displayed end date may leave it out. For timestamp data, a common approach is to use a condition strictly earlier than the day after the end date. This includes every time on the selected final date while excluding midnight on the following date. Check that approach against the meaning of your data and how the end-date cell is entered.

Understand what gets averaged

AVERAGEIFS averages numeric entries in its average range for rows that satisfy every criterion. Text and blank cells in the average range are not treated as numeric values. The function does not average the criteria-range values unless that range is also supplied as the average range. This distinction matters when one column contains a category, such as region, and another contains the numbers to average.

For example, if sales are in B2:B20 and transaction amounts to average are in C2:C20, using B2:B20 as both criteria ranges would test the sales figures against the limits but would still average C2:C20. The result is an average of amounts for qualifying rows, not an average of the values used to define eligibility. If you instead want the qualifying values themselves averaged, use that numeric column as the average range too.

All criteria in an AVERAGEIFS formula are combined with AND logic. Each row must pass the lower-bound test and the upper-bound test. If you add a category condition, such as matching a department, that row must also match the department. To combine separate groups with OR logic, calculate the groups separately or restructure the data; simply adding another criterion pair does not make an OR condition.

Check for errors and unexpected results

If no numeric values meet both limits, Excel returns #DIV/0! because there is nothing to average. That result can mean the limits are reversed, the data lies outside the range, or the criteria do not match the stored values. Check the lower and upper cells first, then inspect a few rows that you expect to qualify.

  • Reversed limits: If the minimum is larger than the maximum, no number can satisfy both conditions. Swap the limits or correct the input cells.
  • Numbers stored as text: Values imported from another system may look numeric but behave as text. Convert them to numbers before relying on numeric comparisons.
  • Unequal range sizes: The average range and each criteria range should have matching dimensions. Misaligned ranges can produce errors or evaluate the wrong rows.
  • Unexpected blanks or errors: Inspect the source data for errors and nonnumeric entries. Correct source issues rather than assuming every visible cell is a usable number.
  • Boundary mismatch: Confirm whether the endpoints should count. Replacing >= and <= with strict comparisons changes which rows qualify.

When a result needs to display a message instead of an error, you can wrap the calculation in IFERROR. With values in C2:C7 and limits in E2 and F2, for example:

=IFERROR(AVERAGEIFS(C2:C7,C2:C7,">="&E2,C2:C7,"<="&F2),"No matching values")

The sample still returns 40. If no values qualify, the formula displays No matching values. This is useful for presentation, but it can also hide other problems that produce errors, so investigate unexpected messages rather than treating them as proof that the data is correct.

Make the calculation easier to maintain

Keeping the limits in separate cells is helpful when users need to explore different ranges. Label the cells Minimum and Maximum, use an appropriate number format, and make the formula’s input area visually distinct. If the data is likely to grow, convert it to an Excel table using Insert > Table. A table expands as rows are added, which can make a calculation easier to maintain than a fixed range, although structured-reference formulas use a different notation.

For a one-time calculation on a small fixed list, ordinary cell ranges are often simplest. For a recurring report, named cells or a table can make the worksheet easier to understand. Whatever structure you choose, verify that the limits and the data use compatible units—for example, dollars with dollars, not dollars with cents—and that the formula includes the full data range.

On Windows, formulas generally use commas between arguments in the examples here. Excel’s argument separator can depend on regional settings; some configurations require semicolons instead. If Excel rejects a correctly written formula, check the list separator configured for your locale. The comparison operators and the logic of the calculation remain the same.

Frequently Asked Questions

Can AVERAGEIF average values between two numbers?
Not with one ordinary AVERAGEIF criterion: it accepts one condition. Use AVERAGEIFS for the lower and upper limits, or calculate a more complex condition through another method if the data calls for it.

Does “between” include the minimum and maximum in Excel?
It depends on the operators. Use >= and <= to include both endpoints. Use > and < to exclude them.

Why does my between-values average return #DIV/0!?
Usually, no numeric entries meet both criteria. Check that the minimum is not greater than the maximum, confirm the data is numeric, and verify that the limits match the intended range.

Can I use cell references for the two limits?
Yes. Join each quoted comparison operator to its limit cell with an ampersand, such as ">="&E2. Changing the limit cell then updates the calculation without changing the formula.

Can I average dates between two dates?
Yes. Use the date column as both criteria ranges and the numeric measure column as the average range. For timestamps, consider an exclusive upper limit equal to the day after the final date so times on that final day are included.

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.