Function Problems

How to Average Values with Partial Text Matches Using AVERAGEIFS

Anum MushtaqAnum Mushtaq Nov 10, 2026 869 views

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.

How to Average Values for Names Containing Text with AVERAGEIFS
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.
Before you start

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.

Solution 1Recommended

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.

1
Select Destination Cell

Click on the first destination cell where you want the average value to appear (for example, H2).

2
Enter the Formula

Type the formula =AVERAGEIFS(B$2:B$10000,$A$2:$A$10000,$G2&"*") into the formula bar and press Enter.

3
Fill the Formula

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.

Use AVERAGEIFS with an Asterisk Wildcard
Relative and Absolute References: The use of absolute references (the $ symbol) ensures that your column and row ranges do not shift incorrectly when copying the formula across multiple columns.
Advanced Spreadsheet Tools

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. 1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your data sets.
  2. 2. Select Output Cell: Click the specific cell where the conditional average result should be displayed.
  3. 3. Input Wildcard Formula: Type your AVERAGEIFS formula, incorporating the asterisk (*) wildcard, directly into the formula bar.
  4. 4. Execute and Fill: Hit Enter to calculate, then drag the fill handle to automatically apply the logic to adjacent cells.
100% compatible with Microsoft Excel formulas and .xlsx file formatsSupports advanced wildcard calculations for complex conditional data analysisLightweight, fast, and features a clean, familiar interface
microsoft office alternative - wps office

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.