Function Problems

Excel Formula to Average Values When Cells Contain Specific Text

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to calculate the average of numeric values in column G, but only for rows where the corresponding cell in column A contains specific text substrings, such as 'TIDS' or 'CEPS'.

Product: Excel
Device & OS: not provided
Scenario: Filtering data and calculating conditional averages based on partial text matches across multiple columns.
Observed behavior: Requires an advanced formula combining multiple functions to evaluate partial text criteria and average the corresponding numeric data.

Before you start

Ensure that the column containing the numeric values (e.g., Column G) is formatted as Numbers and does not contain hidden text characters or errors, which could cause the calculation to fail.

Solution 1Recommended

Use the AVERAGE and FILTER Functions with Hardcoded Criteria

This solution uses an array formula that combines AVERAGE, FILTER, ISNUMBER, and SEARCH to dynamically evaluate rows containing the desired text strings.

The FILTER function creates an array of the matched numbers, while ISNUMBER and SEARCH check if the specific text strings exist anywhere within the target cells. Using the '+' operator between the ISNUMBER functions creates an 'OR' logic condition.

Select the destination cell

Click on the empty cell where you want the calculated average to be displayed.

Input the array formula

Type the formula =AVERAGE(FILTER(G:G,(ISNUMBER(SEARCH("TIDS",A:A))+ISNUMBER(SEARCH("CEPS",A:A))))) into the formula bar.

Execute the calculation

Press the Enter key. Excel will filter column G based on whether column A contains 'TIDS' or 'CEPS', and instantly return the average of those filtered values.

Case Insensitivity: The SEARCH function is not case-sensitive. If you need an exact, case-sensitive match (e.g., strictly uppercase 'TIDS'), use the FIND function instead of SEARCH.

Solution 2

Use Cell References for Dynamic Search Criteria

Instead of typing the search terms directly into your formula, referencing specific cells allows you to quickly change the search criteria without modifying the formula itself.

Advanced Formula Support

Effortlessly Calculate Complex Formulas with WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for advanced array functions, including AVERAGE, FILTER, ISNUMBER, and SEARCH. You can seamlessly perform complex conditional calculations for your data analysis tasks.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your dataset.
2. Enter the formula: Click the cell for your result and input =AVERAGE(FILTER(G:G,(ISNUMBER(SEARCH("TIDS",A:A))+ISNUMBER(SEARCH("CEPS",A:A))))).
3. Get instant results: Press Enter. WPS Spreadsheet will instantly parse the array and return the correct conditional average.

Fully compatible with Microsoft Excel (.xlsx) formats, ensuring your complex formulas work seamlessly.Native support for dynamic array functions like FILTER and SEARCH for powerful data queries.Lightweight architecture ensures fast calculations even when referencing entire columns.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the AVERAGEIFS function instead of FILTER to achieve this?

While AVERAGEIFS is excellent for multiple conditions, using it with 'OR' logic for partial text (e.g., matching '*TIDS*' or '*CEPS*') requires wrapping it in an array and adding extra sum/count logic, which can get complicated. The FILTER method is generally more robust and easier to expand for 'OR' logic.

Why does my formula return a #CALC! error?

A #CALC! error typically means the FILTER function returned an empty array because none of the cells in column A matched your search terms. Ensure the text actually exists in the column and check for typos.

How can I add a third search term to the formula?

To add another condition, insert another addition symbol (+) and another ISNUMBER(SEARCH()) statement inside the FILTER criteria. For example: +ISNUMBER(SEARCH("NEWTERM",A:A)) within the second set of parentheses.

Excel & Data Problems