To average values by date in Excel, use the AVERAGEIF function with the date range as the criteria range and a date cell as the criteria. For example, with dates in A2:A20 and values in B2:B20, the formula =AVERAGEIF(A2:A20, DATE(2025,1,15), B2:B20) returns the average of all values whose date equals January 15, 2025. If you need to average by a date range (e.g., a whole month), use AVERAGEIFS with two criteria, or use a helper column with TEXT or MONTH to group dates.
Why Average by Date Matters
Date-based averaging is essential for analyzing time-series data—sales per day, daily temperatures, website traffic, or inventory levels. Instead of manually filtering rows, Excel functions let you compute averages dynamically. The key is understanding that Excel stores dates as serial numbers (e.g., January 1, 1900 is 1), so comparisons work numerically. This means you can use operators like > or < in criteria, but you must format the criteria as a real date, not text, to avoid errors.
Using AVERAGEIF for a Single Date
The simplest case: you want the average of all values on one specific date. Layout: dates in A2:A20, values in B2:B20. Formula:
=AVERAGEIF(A2:A20, DATE(2025,1,15), B2:B20)
This returns the average of B2:B20 where A2:A20 equals January 15, 2025. The DATE function ensures the criteria is a true date serial. If you have the date in a cell, say D1, use =AVERAGEIF(A2:A20, D1, B2:B20). Note: AVERAGEIF ignores blank cells and text in the average range, but it requires the criteria range to have the same size as the average range.
Averaging by Date Range (e.g., a Month)
To average values for a whole month, use AVERAGEIFS with two criteria. Layout: dates in A2:A20, values in B2:B20. To average all of January 2025, enter the start date in E1 (e.g., 1/1/2025) and end date in E2 (e.g., 1/31/2025). Formula:
=AVERAGEIFS(B2:B20, A2:A20, ">="&E1, A2:A20, "<="&E2)
This returns the average of B2:B20 where A2:A20 is between E1 and E2 inclusive. The ampersand concatenates the comparison operator with the cell reference. For a dynamic month, you could use EOMONTH to get the last day: =AVERAGEIFS(B2:B20, A2:A20, ">="&DATE(2025,1,1), A2:A20, "<="&EOMONTH(DATE(2025,1,1),0)).
Grouping by Week, Month, or Year with a Helper Column
If you need averages for many periods (e.g., every month in a year), add a helper column that extracts the period. For months, in C2 enter =TEXT(A2,"YYYY-MM") and copy down. Then use =AVERAGEIF(C2:C20, "2025-01", B2:B20). For weeks, use =ISOWEEKNUM(A2) to get the ISO week number, then average by that number. For years, use =YEAR(A2). This approach is flexible and avoids complex criteria strings.
Using PivotTables for Quick Date Averages
A PivotTable is often easier for summarizing by date without formulas. Select your data (A1:B20), go to Insert > PivotTable, and place the date field in Rows and the value field in Values. Right-click the date in the PivotTable, choose Group, then select Months, Quarters, or Years. The PivotTable automatically shows average if you change the value field settings to Average (right-click > Summarize Values By > Average). This is ideal for large datasets and interactive exploration. Note that PivotTables require Excel 2010 or later; on Mac, the steps are the same but the ribbon menu is under Insert > PivotTable.
Common Errors and How to Fix Them
- #DIV/0! error: No dates match the criteria. Check that the date format matches your cells—if your dates are text, convert them with
DATEVALUEor use Text to Columns. - Wrong averages due to hidden rows: AVERAGEIF ignores hidden rows? No, it does not. If you filter rows, AVERAGEIF still includes them. Use
SUBTOTALwith function 101 (AVERAGE) on a filtered range, but that averages all visible rows, not by date. For filtered date averages, use a PivotTable. - Text dates: If your date column contains text like "Jan 15", AVERAGEIF will not match a real date. Use
DATEVALUEto convert, or ensure dates are entered as date values. - Blank cells in the average range: AVERAGEIF ignores blanks, but if your average range has zeros, they are included. If you want to ignore zeros, use
AVERAGEIFSwith an additional criteriaB2:B20, "<>0".
Comparing AVERAGEIF vs. AVERAGEIFS vs. SUMPRODUCT
AVERAGEIF is for one condition; AVERAGEIFS handles multiple conditions (like date range). SUMPRODUCT can also average by date but is more complex: =SUMPRODUCT((A2:A20>=DATE(2025,1,1))*(A2:A20<=DATE(2025,1,31))*B2:B20)/SUMPRODUCT((A2:A20>=DATE(2025,1,1))*(A2:A20<=DATE(2025,1,31))). This works but is harder to read and can be slower on large data. For most cases, AVERAGEIFS is the recommended choice because it is clearer and faster. If you have Excel 365, you could also use FILTER with AVERAGE: =AVERAGE(FILTER(B2:B20, (A2:A20>=DATE(2025,1,1))*(A2:A20<=DATE(2025,1,31)))), but that requires dynamic array support.
Frequently Asked Questions
How do I average by date in Excel without a helper column? Use AVERAGEIFS with two criteria for a range, or AVERAGEIF for a single date. For grouping by month, you would need a helper column or a PivotTable, because AVERAGEIF cannot extract the month from a date directly.
Why does my AVERAGEIF return #VALUE! instead of a number? This usually happens when the criteria range and average range are different sizes, or when the criteria is text that cannot be interpreted as a date. Ensure both ranges have the same number of rows and use DATE() or a cell reference for the criteria.
Can I average by date and another condition, like product? Yes, use AVERAGEIFS with multiple criteria pairs. For example, with product names in C2:C20, use =AVERAGEIFS(B2:B20, A2:A20, ">="&E1, A2:A20, "<="&E2, C2:C20, "Widget").
Does AVERAGEIF work with dates in the future? Yes, it works with any date serial number. Just ensure your criteria date is within the range you expect.
How do I average by day of the week? Add a helper column with =TEXT(A2,"dddd") to get the weekday name, then use AVERAGEIF on that column. For example, =AVERAGEIF(C2:C20,"Monday",B2:B20).
What is the fastest way to average by date for a huge dataset? Use a PivotTable with the date grouped by month or year. It handles thousands of rows efficiently and updates instantly when you refresh.
Related
- Split a String in C++: Methods and Examples
- String to Int in C++: Safe Conversion Methods
- Split in Oracle SQL: Turn Delimited Text Into Rows
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Protected Sheet Sort: Settings, Limits, and FixesTo sort a protected Excel worksheet, choose Review > Protect Sheet, allow users to sort, and protect the…
- DATEDIF in Excel: Is It Still Available?Yes—DATEDIF still works in Microsoft Excel for Microsoft 365, even though it is a legacy function that may…
- Revit IF Formula Examples for Family ParametersIn a Revit family, an IF formula tests a condition and returns one value when it is true…
- Excel Formula for Grouping Data by CategoryIn Excel for Microsoft 365, use UNIQUE to list each category once and FILTER to return the records…
- Excel NOW Time: Current Time, Formulas, and ShortcutsTo display the current date and time in Excel, enter =NOW() in a cell; to show only the…
- Excel Formula Solver: Goal Seek, Solver, and How to Use ThemExcel does not have a worksheet function called “formula solver.” To make Excel find an input that produces…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.