Formula Errors

Calculate an Average Before or After a Date in Excel with AVERAGEIFS

Camila MilosovichCamila Milosovich Nov 18, 2026 871 views

Question details

Calculate an average for records before or after a specified date and check why AVERAGEIFS returns a division-by-zero error.

Calculate an Average Before or After a Date in Excel with AVERAGEIFS
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet uses AVERAGEIFS to average numeric values based on a date and possibly other criteria.
Observed behavior
Excel may return #DIV/0! when no matching rows contain numeric values to average.
Before you start

Identify the date column, the numeric values to average, and any additional conditions. Check that the date cells contain actual spreadsheet dates rather than text and that all ranges cover the same rows.

Solution 1Recommended

Check the AVERAGEIFS Date Criteria and Matching Values

Use this approach to calculate the average and diagnose a #DIV/0! result.

The examples use dates in A2:A100 and values to average in B2:B100. Replace these ranges with the ranges in your worksheet, keeping their sizes aligned.

1
Enter a date-based formula

Select an empty cell and enter =AVERAGEIFS(B2:B100,A2:A100,"<"&DATE(2025,1,1)) to average values dated before January 1, 2025. For dates after that day, use =AVERAGEIFS(B2:B100,A2:A100,">="&DATE(2025,1,2)).

2
Check for matching rows

In an empty cell, enter =COUNTIFS(A2:A100,"<"&DATE(2025,1,1)) for the before-date example. If your AVERAGEIFS formula has other criteria, add the same criteria ranges and conditions to COUNTIFS. A zero result means no rows meet the complete set of conditions.

3
Verify dates and values

Enter =ISNUMBER(A2) in an empty cell to check a sample date, then inspect the matching cells in column B. Correct dates stored as text and ensure at least one matching value is numeric.

Check the AVERAGEIFS Date Criteria and Matching Values
Dates with times: The after-date example starts at January 2, so it excludes all times on January 1. Using >DATE(2025,1,1) would include January 1 entries with a time later than midnight.
Calculate averages in WPS Spreadsheet

Use AVERAGEIFS with Dates in WPS Spreadsheet

Open your worksheet in WPS Spreadsheet and use AVERAGEIFS to average values before or after a date. Check matching rows and numeric values if the formula returns #DIV/0!.

  1. 1. Open the worksheet: In WPS Spreadsheet, use File > Open to select your workbook.
  2. 2. Enter the formula: Select an empty cell and enter =AVERAGEIFS(B2:B100,A2:A100,"<"&DATE(2025,1,1)), adjusting the ranges and date to match your data.
  3. 3. Check the result: Press Enter. If you see #DIV/0!, use COUNTIFS in an empty cell to check the date and any other conditions, then verify that matching values are numeric.
  4. 4. Save the workbook: Use File > Save or Save As to keep the formula in your workbook.
Calculate date-based averages with AVERAGEIFS.Open and edit Microsoft Excel XLSX and XLS files.Check matching conditions with COUNTIFS.Use a familiar spreadsheet interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why does AVERAGEIFS return #DIV/0!?

The formula returns #DIV/0! when there are no numeric values to average among the rows that meet all criteria. Check the conditions with COUNTIFS and inspect the matching average-range cells.

How do I include the specified date in the average?

Use <= for dates on or before the specified date, or >= for dates on or after it. For example, =AVERAGEIFS(B2:B100,A2:A100,">="&DATE(2025,1,1)) includes January 1, 2025 and later dates.

Can I use a date from another cell as the criterion?

Yes. If D1 contains a valid date, use a criterion such as "<"&D1 in AVERAGEIFS to average records before that date.

Why do matching rows still produce a #DIV/0! error?

COUNTIFS can find matching rows even if their average-range cells contain no numeric values. Check those cells and convert values stored as text to numbers where appropriate.