Calculate an Average Before or After a Date in Excel with AVERAGEIFS
Question details
Calculate an average for records before or after a specified date and check why AVERAGEIFS returns a division-by-zero error.

- 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.
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.
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.
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)).
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.
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.

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. Open the worksheet: In WPS Spreadsheet, use File > Open to select your workbook.
- 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. 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. Save the workbook: Use File > Save or Save As to keep the formula in your workbook.

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.




