How to Average Values with Partial Text Matches Using AVERAGEIFS
Question details
The user needs to calculate the average of values across multiple columns by matching target names with a column where names contain additional text.

- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Attempting to calculate conditional averages when the criteria column contains extra characters alongside the target string.
- Observed behavior
- Needs to match target names with cells that have additional text appended, and return a blank instead of an error if no numeric data is found.
Ensure that your target names accurately reflect the starting text of the names in your source column, as we will be using the asterisk (*) wildcard to match trailing characters.
Use AVERAGEIFS with an Asterisk Wildcard
Append an asterisk (*) wildcard to your criteria range to average cells where the text starts with your target name.
The AVERAGEIFS function allows you to average cells based on multiple criteria. By combining it with a wildcard (*), you can accurately match cells that contain specific text followed by any other characters, without manually cleaning the data.
Click on the first destination cell where you want the average value to appear (for example, H2).
Type the formula =AVERAGEIFS(B$2:B$10000,$A$2:$A$10000,$G2&"*") into the formula bar and press Enter.
Click and hold the small square at the bottom right corner of the cell (the fill handle), and drag it across your target columns, then down to apply the formula to the remaining rows.

Wrap AVERAGEIFS in IFERROR to Handle Blanks
Use the IFERROR function to return a blank cell instead of an error when no matching numeric data exists.
Use WPS Spreadsheet for Complex Data Calculations
WPS Spreadsheet fully supports advanced functions like AVERAGEIFS, IFERROR, and wildcards. It handles complex data analysis with ease and is completely compatible with your existing workbooks.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your data sets.
- 2. Select Output Cell: Click the specific cell where the conditional average result should be displayed.
- 3. Input Wildcard Formula: Type your AVERAGEIFS formula, incorporating the asterisk (*) wildcard, directly into the formula bar.
- 4. Execute and Fill: Hit Enter to calculate, then drag the fill handle to automatically apply the logic to adjacent cells.

Frequently Asked Questions
What does the asterisk (*) wildcard do in AVERAGEIFS?
The asterisk wildcard represents any sequence of characters. For example, using 'Apple*' as your criteria will match 'Apple', 'Apple Pie', and 'Apple123' in your data source.
Can I use a wildcard at the beginning of the text instead?
Yes, you can use "*"&G2 to match text that ends with your target name, or "*"&G2&"*" to match text that contains your target name anywhere within the cell.
Why is my AVERAGEIFS returning a #DIV/0! error?
This error occurs when the function cannot find any cells that meet your specified criteria, meaning it tries to divide the sum by zero. You can prevent this error from displaying by wrapping your formula in an IFERROR function.




