Function Problems

Calculate an Excel Average for Multiple Criteria and Three Months

Ayan MasoodAyan Masood Nov 18, 2026 870 views

Question details

Calculate the average of sales values for three selected months and several content types.

Calculate an Excel Average for Multiple Criteria and Three Months
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.
Before you start

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.

Solution 1Recommended

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.

1
Set the month criteria

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.

2
Enter the formula

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

3
Confirm and check the result

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.

4
Adjust the ranges if needed

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.

Use an array formula to average matching months and content types
No matching numeric values: If no rows meet both criteria and contain a numeric value in column P, AVERAGE returns #DIV/0!. Check the criteria and source data.
Calculate with WPS Spreadsheets

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. 1. Open the workbook: In WPS Spreadsheets, select Open and choose your Excel workbook.
  2. 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. 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. 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.
Combine multiple month and content-type criteria in one formulaOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with bounded ranges to limit unnecessary calculations
microsoft office alternative - wps office

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.