With dates in A2:A100, values to average in B2:B100, a start date in E2, and an end date in F2, use =AVERAGEIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<="&F2). This averages the values whose dates fall on or between the two boundary dates, inclusive.
What AVERAGEIFS does
AVERAGEIFS calculates the average of numeric values that meet one or more conditions. For a date range, you give it the values to average, then use the date column twice: once to set the earliest allowed date and once to set the latest. The criteria operators are joined to the date cells with ampersands, so Excel compares each date with the boundary stored in the cell.
In the example above, the first date condition means “on or after E2,” and the second means “on or before F2.” The two tests apply to the same date range, so a row is included only when it passes both. Dates in E2 and F2 are easier to change than dates typed into the formula, and they make the date window visible to anyone reviewing the worksheet.
AVERAGEIFS is available in modern desktop Excel, including Microsoft 365. It is designed for multiple conditions; if you need only one condition, Excel also has AVERAGEIF. AVERAGEIFS is usually the clearer choice when both a start and end date must be enforced.
Set up the ranges and date cells
Keep each record on one row. The date range and average range must cover the same number of rows and align with each other. For example, if A2 contains a record’s date, B2 should contain the value associated with that date. If the date range is A2:A100, the average range should be B2:B100—not B3:B101.
Enter the start date in E2 and the end date in F2 as genuine Excel dates. You can type a date in a familiar format, then check it by changing the cell’s number format to General; a real Excel date displays as a serial number. A date that merely looks like a date but is stored as text may not compare as expected.
- Use one date column for the date each value belongs to.
- Put the numeric values to average in a corresponding column.
- Use cells for the start and end boundaries, and make sure the start date is not later than the end date.
- Keep headers out of the ranges, unless the header is intentionally part of a separate table setup.
Excel stores dates as serial values, which is why comparison operators work on real dates. The display format—such as 3-Nov-2026 or 11/3/2026—does not change the underlying date. However, date entry conventions can vary by Windows regional settings, so an ambiguous entry may be interpreted differently than intended. A clearly written date or a date selected from the date picker, where available, can help avoid that confusion.
Inclusive dates and the meaning of “between”
The opening formula treats both endpoints as included. A row dated exactly on the start date or exactly on the end date qualifies. That matches the usual interpretation of “between these two dates,” particularly when a report should include the full first and last calendar days.
Sometimes the desired rule is different. To exclude the start date, change the first comparison from greater-than-or-equal to greater-than. To exclude the end date, change the second comparison from less-than-or-equal to less-than. Choose the rule based on the reporting requirement rather than assuming every use of “between” means the same thing.
For example, a date window that begins on January 1 and ends on January 31 is inclusive in the ordinary date-only formula. A value recorded on January 31 qualifies; a value on February 1 does not. The cells holding the boundaries should contain actual dates, not text such as “January.”
Worked example with a checked result
Suppose A2:A6 contains January 1, January 5, January 10, January 15, and January 20, while B2:B6 contains 10, 20, 30, 40, and 100. With E2 containing January 5 and F2 containing January 15, the formula at the start of this article includes the second, third, and fourth rows. Their values total 90, and 90 divided by 3 is 30, so the returned average is 30.
The January 1 and January 20 records are left out because they fall outside the selected date window. If the end date in F2 were January 10, the January 10 row would still be included, because the formula uses an inclusive upper boundary. That boundary behavior is one reason it is worth confirming whether your report should include the end date.
Including every time on the end date
A date-time value contains both a date and a time. If the end boundary in F2 is stored as midnight at the start of that day, an inclusive comparison with F2 can omit records later on the same day, such as 3:00 p.m. This is a common cause of a result that appears to miss the last day even though the formula looks correct.
For date-time records, use a less-than comparison against the day after the end date. With timestamps in A2:A100, values in B2:B100, the start date in E2, and the final calendar date in F2, use =AVERAGEIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<"&(F2+1)). This includes every time on F2 but excludes midnight at the start of the following day. E2 and F2 should represent calendar dates, and the source timestamps should be real Excel date-time values.
This approach is often preferable to trying to set an end time such as 11:59:59 p.m. Excel times can include fractions smaller than a second, so a manually chosen final time could still leave out a value. Using the next day as an exclusive boundary avoids that gap.
Filtering for a month or another changing period
The same two-boundary approach works for a month, quarter, or reporting period. For a month, set the first boundary to the first day of that month and the second boundary to the first day of the next month, then use a less-than comparison for the second boundary. This makes the whole month inclusive without needing to calculate its final calendar date.
For example, with dates in A2:A100, values in B2:B100, the first day of the target month in E2, and the first day of the next month in F2, use =AVERAGEIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<"&F2). If E2 is April 1 and F2 is May 1, records from April 1 up to—but not including—May 1 are included. The formula works for months of different lengths, including February.
For a quarter or custom period, use the same rule: the beginning is inclusive and the first day after the period is exclusive. This is useful when dates also contain times, because it avoids accidentally excluding later timestamps on the final day.
Common problems and fixes
- No qualifying numbers: AVERAGEIFS returns a divide-by-zero error when no rows meet the criteria or when the qualifying rows contain no numeric values to average. Check the date window and confirm that the matching cells in the average range are numbers.
- Dates stored as text: Text entries that resemble dates may not compare like Excel date serials. Convert the source column to real dates and check whether sorting or number formatting behaves as expected.
- Unequal range sizes: The average range and each criteria range should have matching dimensions. A range that begins or ends on a different row can produce an error or a result based on the wrong records.
- Wrong boundary operator: Using a strict greater-than or less-than operator excludes records exactly on that boundary. Use inclusive operators for date-only endpoints when the first and last dates should count.
- Time portions in the source: If a date column contains timestamps, an end-date criterion set to midnight can exclude most of the final day. Use the next-day exclusive boundary for a full-day interval.
- Regional date interpretation: Dates typed with numbers can be ambiguous across regional settings. Confirm the value Excel stored instead of relying only on how it appears on screen.
AVERAGEIFS ignores blank cells and text in the average range when calculating the average, but a row still has to meet the date conditions. If some qualifying values are stored as text, they will not contribute as numeric values. Fix the underlying data when possible rather than changing the date criteria to compensate.
Using additional criteria
You can add other conditions alongside the date window—for example, to average sales for one region or category only. Each extra condition needs its own criteria range and criterion. All conditions are combined as an AND test: a row must meet the date boundaries and every additional condition to be included.
With dates in A2:A100, sales in B2:B100, regions in C2:C100, the date boundaries in E2 and F2, and the chosen region in G2, use =AVERAGEIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<="&F2,C2:C100,G2). This averages only the sales entries whose dates fall within the inclusive window and whose region matches G2. The average range and every criteria range cover rows 2 through 100, so they remain aligned.
Criteria can refer to cells, as in this example, or can be fixed text or numbers. Referencing cells is helpful for interactive reports because you can change a region or date without editing the formula. Ensure that category text matches the source values; extra spaces or inconsistent spelling can prevent a row from qualifying.
AVERAGEIFS compared with alternatives
Use AVERAGEIFS when the task is to average values based on a date interval and possibly other conditions. AVERAGEIF handles one criterion, so it can test a single date boundary but is not as direct for a two-sided interval. Two AVERAGEIF calculations are not a reliable substitute for a combined date window because they do not automatically restrict the average to rows meeting both boundaries.
A PivotTable is a useful alternative when you want to summarize many categories or change the grouping interactively. You can group date fields by month or another period, then compare averages across groups. A formula is often more convenient for a single summary cell that updates from specified boundary cells; a PivotTable is often more convenient for exploring a larger dataset. Either approach depends on having consistent, usable source dates and numeric values.
For a one-off result, filtering the date column and calculating an average may be sufficient, but that process is easier to repeat incorrectly than a formula tied to explicit start and end cells. If the report is recurring, a formula or PivotTable makes the criteria easier to review and update.
Frequently Asked Questions
Can AVERAGEIFS use the same date range twice?
Yes. Use the date column once for the lower boundary and again for the upper boundary. Both criteria must be true for a row to be included.
Does AVERAGEIFS include the start and end dates?
It does when the criteria use greater-than-or-equal for the start and less-than-or-equal for the end. If you use strict greater-than or less-than operators, the corresponding boundary date is excluded.
Why does my formula leave out the last day?
If the source has date-time values, a boundary date usually represents midnight at the beginning of that date. Use a less-than criterion against the day after the desired end date to include all times on the final day.
What does a divide-by-zero error mean in AVERAGEIFS?
Usually, no rows meet the criteria, or the matching average-range cells contain no numbers. Verify the dates and criteria, then check that qualifying values are numeric.
Can I type the dates directly into the formula?
You can construct date criteria with Excel date values, but boundary cells are easier to maintain and less vulnerable to date-format ambiguity. Use real Excel dates in those cells, and make sure the formula’s operators match the intended inclusive or exclusive boundaries.
Related
- Excel Slicers for Multiple Tables: How to Connect Them
- Excel Link Data to a PivotTable: How It Works
- Excel Lagging and Not Responding? Causes and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel LAMBDA vs LET: Differences, Uses, and ExamplesExcel’s LET function gives names to values inside one formula, while LAMBDA lets you define a reusable function…
- BMI Calculator in Excel: Formula and SetupTo make a BMI calculator in Excel, enter weight in kilograms in B2 and height in metres in…
- Capitalize the Whole Word in Excel: Formulas and TipsTo capitalize every letter in a word or cell in Excel, use the UPPER function: if the original…
- COUNTIF Excel Formula for Text: Exact and Partial MatchesTo count cells containing text in Excel, use =COUNTIF(A2:A20,"apple") for an exact match, or =COUNTIF(A2:A20,"*apple*") when the text…
- XLOOKUP Spreadsheet Examples for Everyday Excel TasksXLOOKUP finds a value in one range and returns the corresponding value from another range; for example, with…
- Excel Filter by Cell Value: Shortcut, Steps, and TipsIn Excel for Microsoft 365 on Windows, there is no single dedicated shortcut that immediately filters a list…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.