Function Problems

How to Calculate an Excel Average for a Specific Month

Chanuka Geekiyanage Oct 13, 2026 871 views

Question details

The user needs to calculate the average of values corresponding to a specific month from a dataset containing dates.

How to Calculate an Average for a Specific Month in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Finding a monthly average from a list of date records where direct application of MONTH() inside AVERAGEIF fails.
Observed behavior: The AVERAGEIF function returns an error or fails because it cannot use MONTH() directly as the criteria range, requiring a physical cell range instead of an array.

Before you start

Ensure that your dates are formatted as actual Excel date values and not as plain text; otherwise, the MONTH function and PivotTable grouping features will not work correctly.

Solution 1Recommended

Use an Array Formula with AVERAGE and IF

This is a single-formula solution that avoids altering your data structure by calculating the monthly average directly in memory.

Because AVERAGEIF requires a physical cell range for its criteria, it rejects the MONTH function. By combining AVERAGE and IF into an array formula, we bypass this limitation.

Select the destination cell

Click the cell where you want the final monthly average to appear.

Enter the combination formula

Type =AVERAGE(IF(MONTH(F6:F17)=3,G6:G17)), replacing F6:F17 with your specific date range, 3 with your target month number (e.g., 3 for March), and G6:G17 with the values you want to average.

Confirm the array formula

If you are using Microsoft 365 or Office 2021, simply press Enter. For earlier versions of Excel, you must press Ctrl+Shift+Enter to evaluate the array formula correctly.

Use an Array Formula with AVERAGE and IF

Ctrl+Shift+Enter Requirement: When you press Ctrl+Shift+Enter in older Excel versions, Excel automatically wraps the formula in curly braces {}. Do not type these braces manually.

Solution 2

Extract the Month Using a Helper Column

By extracting the month into a separate column first, you can satisfy AVERAGEIF's requirement for a standard cell range.

Solution 3

Create a PivotTable Grouped by Month

This approach is ideal if you need to calculate averages for multiple months or years simultaneously without writing complex formulas.

Advanced Data Analysis

Use WPS Spreadsheet to Analyze Monthly Data Quickly

WPS Spreadsheet seamlessly handles advanced array formulas, AVERAGEIFS logic, and PivotTable groupings, allowing you to calculate specific monthly averages with zero friction.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the dates and corresponding values.
2. Apply the array formula: Select an empty cell and enter the formula: =AVERAGE(IF(MONTH(F6:F17)=3, G6:G17)).
3. Confirm with a shortcut: Press Ctrl+Shift+Enter to calculate the specific month's average instantly.

Fully compatible with Microsoft Excel formulas, functions, and file formats.Native support for advanced array formulas and combinations like AVERAGE and IF.Intuitive PivotTable interface for instant monthly and yearly data summarization.Lightweight, fast, and free to use for both basic and complex data analysis.

Frequently Asked Questions

Why does AVERAGEIF(MONTH(A1:A10), 1, B1:B10) return an error?

The AVERAGEIF function strictly requires a physical cell range for its criteria argument. Because MONTH(A1:A10) generates an in-memory array rather than referencing actual cells on the worksheet, Excel rejects it. You must use an array formula like =AVERAGE(IF(...)) or create a helper column.

How can I average data for a specific month and year?

You can modify the array formula to include the year criteria using multiplication: =AVERAGE(IF((MONTH(A2:A20)=3)*(YEAR(A2:A20)=2023), B2:B20)). Remember to use Ctrl+Shift+Enter in older versions. Alternatively, a PivotTable grouped by both 'Months' and 'Years' provides a fast summary.

Can I use AVERAGEIFS for this task without extracting the month?

Yes, but you must define specific start and end dates instead of using the MONTH function. For example, to average data for March 2023, you can use: =AVERAGEIFS(B2:B20, A2:A20, ">=1/3/2023", A2:A20, "<=31/3/2023").

Excel & Data Problems