Calculate an Average with Multiple Criteria in Excel or Google Sheets
Question details
Calculate the average cost for each category using a month and several permitted text values as criteria.

- Product
- Microsoft Excel or Google Sheets
- Device & OS
- not provided
- Scenario
- A sales-data sheet contains costs, month and category fields, and a text field that may contain normal, normal 1 through normal 5, or short.
- Observed behavior
- The desired result is one average across all matching rows, including any of the allowed text values.
Confirm which columns contain the month, category, cost, and text values, and choose an empty column for a helper formula. The example assumes that the month field contains a value that exactly matches the selected month; dates stored as individual days need a date-range condition instead.
Use AVERAGEIFS with a helper column for allowed text values
Use a helper column to include several alternative text values while calculating one weighted average across all matching rows.
This example uses the Inf_Sales_Data sheet: column A for month, B for an additional criterion, AB for category, T for text, and J for cost. On the results sheet, B1 holds the month, B2 holds the additional criterion, and A89 holds the category. Adjust these references to match your data.
On Inf_Sales_Data, enter this formula in an empty helper cell such as AC2: =OR(T2="normal",T2="normal 1",T2="normal 2",T2="normal 3",T2="normal 4",T2="normal 5",T2="short"). Fill the formula down through AC1000.
On the results sheet, select the cell where you want the average and enter: =AVERAGEIFS(Inf_Sales_Data!$J$2:$J$1000,Inf_Sales_Data!$A$2:$A$1000,$B$1,Inf_Sales_Data!$B$2:$B$1000,$B$2,Inf_Sales_Data!$AB$2:$AB$1000,A89,Inf_Sales_Data!$AC$2:$AC$1000,TRUE).
Enter each category in column A of the results sheet, then copy the formula down beside the category list. The A89 reference changes for each row, while B1 and B2 remain fixed.
If your spreadsheet locale requires semicolons, replace the commas in both formulas with semicolons before entering them. Adjust the row limits consistently if your data extends beyond row 1000.

Average Costs by Multiple Criteria in WPS Spreadsheets
Use a helper column and AVERAGEIFS in WPS Spreadsheets to average costs for the selected month, category, and allowed text values.
- 1. Open the workbook: Open your spreadsheet in WPS Spreadsheets and identify the cost, month, category, and text columns.
- 2. Create the helper column: In an empty column on Inf_Sales_Data, enter =OR(T2="normal",T2="normal 1",T2="normal 2",T2="normal 3",T2="normal 4",T2="normal 5",T2="short") in AC2, then fill it down to the last data row.
- 3. Calculate the average: On the results sheet, select an output cell and enter =AVERAGEIFS(Inf_Sales_Data!$J$2:$J$1000,Inf_Sales_Data!$A$2:$A$1000,$B$1,Inf_Sales_Data!$B$2:$B$1000,$B$2,Inf_Sales_Data!$AB$2:$AB$1000,A89,Inf_Sales_Data!$AC$2:$AC$1000,TRUE). Adjust the references and separators for your workbook.
- 4. Copy for other categories: Place category names in column A and fill the output formula down beside them to calculate each category's average.

Frequently Asked Questions
Why does AVERAGEIFS return #DIV/0!?
The error can occur when no matching rows contain numeric costs. Check the values in the criteria cells, the helper column, and the cost range, and confirm that the month value matches the source data.
How do I average dates within a month rather than matching one date?
Use two conditions on the date column: one for dates greater than or equal to the first day of the month and another for dates before the first day of the next month. For example, use ">="&$B$1 and "<"&EDATE($B$1,1) as criteria, with B1 containing the month's first day.
Why does my formula need semicolons instead of commas?
The argument separator depends on your spreadsheet's locale settings. If commas are rejected, replace them with semicolons in both the OR and AVERAGEIFS formulas.
Can I use normal* instead of listing every allowed value?
A wildcard such as normal* also matches other text beginning with normal, so use it only if those additional values should be included. List the exact values in the helper column when the allowed set is limited.




