Calculate an Excel Average for Multiple Criteria and Three Months
Question details
Calculate the average of sales values for three selected months and several content types.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to average values for any of three selected months, provided each row also matches one of four content types.
- Observed behavior
- AVERAGEIFS does not produce the required result when the month and content-type criteria are supplied as OR conditions.
Check that column B contains values matching the selected months in G216:I216, column T contains the content types, and column P contains the values to average. If column B contains individual dates rather than month labels or matching month-start dates, adjust the criteria to use date ranges instead of exact matches.
Use an array formula to average matching months and content types
Use this formula when a row must match any selected month and any listed content type.
The plus signs combine alternatives within each group as OR conditions. Multiplication requires a match in both groups, and ISNUMBER excludes blank or text values from the average.
The formula matches the values in column B exactly; it does not automatically identify the past three calendar months.
On the worksheet where you will enter the formula, put the three month values in cells G216, H216, and I216. Make sure they match the values stored in Inf_Sales_Data column B.
Select the result cell and enter: =AVERAGE(IF(((Inf_Sales_Data!$B$2:$B$250=$G$216)+(Inf_Sales_Data!$B$2:$B$250=$H$216)+(Inf_Sales_Data!$B$2:$B$250=$I$216))*((Inf_Sales_Data!$T$2:$T$250="Feedpost")+(Inf_Sales_Data!$T$2:$T$250="Reel")+(Inf_Sales_Data!$T$2:$T$250="Short")+(Inf_Sales_Data!$T$2:$T$250="Short 2"))*ISNUMBER(Inf_Sales_Data!$P$2:$P$250),Inf_Sales_Data!$P$2:$P$250))
Press Enter in Microsoft 365 or Excel 2021 and later. In older Windows versions of Excel, confirm the formula with Ctrl+Shift+Enter. Check that the result includes only the intended rows.
Edit the formula in the formula bar to extend all three ranges to the same last row if your data goes beyond row 250. Keep the ranges bounded rather than using entire columns.

Average selected months and content types in WPS Spreadsheets
Open your workbook in WPS Spreadsheets and use an array formula to average numeric values that match both the selected months and the content types.
- 1. Open the workbook: In WPS Spreadsheets, select Open and choose your Excel workbook.
- 2. Check the criteria: On the worksheet for the result, enter the three exact month values in G216:I216 and check the source columns on the Inf_Sales_Data sheet.
- 3. Enter the array formula: Select the result cell and enter the AVERAGE(IF(...)) formula from the solution above. Adjust the row limits and content-type text in the formula bar if needed.
- 4. Confirm and save: Press Enter to calculate; if your WPS version requires array-formula confirmation, use its array-formula entry method. Check the result, then select File > Save As to save the workbook.

Frequently Asked Questions
Why doesn't AVERAGEIFS handle these OR conditions directly?
AVERAGEIFS combines separate criteria as AND conditions. To match alternatives within both the month and content-type groups, use the AVERAGE(IF(...)) array formula shown above.
Does the formula automatically find the past three months?
No. It matches the values in G216:I216 exactly against column B. Update those cells for the desired months, or use date-range criteria if column B contains individual dates.
Why does the formula return #DIV/0!?
There may be no matching rows with numeric values in column P. Check G216:I216, the content-type text in column T, and the numeric values in column P.
How do I include more rows or another content type?
In the formula bar, extend the B, T, and P ranges to the same last row. To add a content type, add another comparison inside the column T group, joined with a plus sign.




