Calculate Month-to-Date Average from Unique Dates in Excel
Question details
The user needs to calculate a month-to-date (MTD) average from a production report containing duplicate dates, counting each reporting date only once based on a specific cell's date.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Generating a production report where multiple products are recorded on the same date, requiring a distinct count of days for an accurate MTD average.
- Observed behavior
- The goal is to calculate the average metrics month-to-date by dividing the total production by the number of unique days, rather than the total number of entries.
Ensure your date column contains valid date formats rather than text, and identify the cell reference that holds your target reporting date.
Use a PivotTable to Group by Date
A PivotTable is the easiest way to consolidate duplicate dates and calculate accurate month-to-date metrics without complex formulas.
By grouping dates in a PivotTable, Excel automatically condenses duplicate entries into single rows. This allows you to easily calculate daily totals and subsequent averages without needing to write complex array formulas.
Highlight your entire production report data range, including the headers for Date and Production Metrics.
Navigate to the 'Insert' tab on the ribbon and click 'PivotTable'. Choose to place it on a new or existing worksheet.
Drag the 'Date' field into the 'Rows' area. The PivotTable will automatically group your data by unique dates, ignoring duplicates.
Drag your production metric field into the 'Values' area. You can then add a grand total or use the PivotTable's calculated fields to find the Month-to-Date average.
Add the 'Date' field to the 'Filters' area (or use a Timeline) to restrict the view to your specific reporting month, giving you the exact MTD result.

Calculate with UNIQUE, FILTER, and SUMIFS Functions
Use dynamic array functions to extract unique dates and calculate the average conditionally based on a specific date cell.
Easily Calculate Unique Averages with WPS Spreadsheet
WPS Spreadsheet offers robust PivotTable features and full support for dynamic array functions like UNIQUE and FILTER, making complex MTD calculations simple.
- 1. Open your report: Launch WPS Spreadsheet and open your production report containing the duplicate dates.
- 2. Insert a PivotTable: Highlight your data table, navigate to the Insert tab, and click PivotTable.
- 3. Group unique dates: Drag the Date field into the Rows section to automatically display only unique dates.
- 4. Filter and average: Use the built-in Date filters to specify your Month-to-Date range and analyze your data.

Frequently Asked Questions
Why does the standard AVERAGE function give me the wrong result for duplicate dates?
The standard AVERAGE function counts every single row as a separate entity. If you have multiple products on the same date, it inflates the denominator (the number of days), artificially lowering the daily average. You must isolate unique dates to calculate a true daily or MTD average.
How can I count the number of unique dates in a specific month?
You can use a combination formula like =COUNTA(UNIQUE(FILTER(A2:A100, MONTH(A2:A100)=MONTH(C1)))) where A2:A100 is your date range and C1 contains a date in your target month.
Can I group dates by month automatically in a PivotTable?
Yes. Right-click any date in the PivotTable's Row area, select 'Group', and choose 'Months'. This instantly consolidates your daily data into monthly summaries.




