Function Problems

Calculate an Average with Multiple Criteria in Excel or Google Sheets

Huma Ashraf ChHuma Ashraf Ch Nov 18, 2026 870 views

Question details

Calculate the average cost for each category using a month and several permitted text values as criteria.

Calculate an Average with Multiple Criteria in Excel or Google Sheets
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.
Before you start

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.

Solution 1Recommended

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.

1
Mark allowed text values

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.

2
Enter the average formula

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

3
Apply the formula to categories

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.

4
Adjust separators if needed

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.

Use AVERAGEIFS with a helper column for allowed text values
Do not add separate averages: Adding or simply averaging separate AVERAGEIFS results does not generally produce the overall average when groups contain different numbers of matching costs. The helper-column approach averages the matching numeric cost cells together.
Calculate with WPS Spreadsheets

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. 1. Open the workbook: Open your spreadsheet in WPS Spreadsheets and identify the cost, month, category, and text columns.
  2. 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. 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. 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.
Combine multiple allowed text values in one average.Work with Microsoft Excel XLSX and XLS spreadsheet files.Use familiar spreadsheet formulas and cell references.Edit spreadsheets in a free, easy-to-use office suite.
microsoft office alternative - wps office

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.