Formula Errors

How to Use a Cell Reference Inside Excel SUMIFS Criteria with Wildcards

Emma Brown Oct 12, 2026 869 views

Question details

The user needs to use a dynamic cell reference combined with wildcards as the criteria for a SUMIFS formula, instead of hard-coding the search string.

How to Use a Cell Reference Inside Excel SUMIFS Criteria

Product: Excel
Device & OS: not provided
Scenario: Creating a SUMIFS function to calculate totals based on partial text matches stored in a specific cell (e.g., searching for a lot number).
Observed behavior: Typing the cell reference directly inside the wildcard quotes (e.g., "*J53*") causes the formula to search for the literal text "J53" rather than evaluating the value contained in cell J53.

Before you start

Ensure that the referenced cell contains the exact text or number you want to search for, without any accidental leading or trailing spaces that could affect the match.

Solution 1Recommended

Concatenate Wildcards with the Cell Reference

Use the ampersand (&) operator to dynamically join wildcard characters with your cell reference outside of the quotation marks.

When you place a cell reference inside quotation marks, Excel treats it as a static text string. By breaking the wildcards into their own quotation marks and linking them to the cell reference using the ampersand (&) symbol, Excel will correctly evaluate the underlying value of the cell.

Select your target cell

Click on the cell where you want the SUMIFS calculation result to appear and type '=SUMIFS(' to begin your formula.

Enter the sum and criteria ranges

Select the range of cells you want to sum, followed by a comma, and then select the range where you want to apply your criteria.

Format the criteria using ampersands

For the criteria argument, type "*" to start with a wildcard, then type the & symbol, select your cell reference (e.g., J53), type another & symbol, and end with "*". The criteria should look exactly like this: "*"&J53&"*"

Complete the formula

Close the parentheses and press Enter. The formula will now dynamically search for any text containing the value stored in cell J53.

Concatenate Wildcards with the Cell Reference

Dynamic Updates: Whenever you change the value inside cell J53, your SUMIFS formula will automatically update its calculation without requiring any edits to the formula itself.

Solution 2

Use a Helper Cell to Store the Wildcard Criteria

Create the wildcard text string in a separate cell and reference that cell directly in your SUMIFS formula.

Easily Calculate Complex Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like SUMIFS, wildcards, and text concatenation. You can handle dynamic criteria effortlessly while enjoying a smooth, lightweight, and highly compatible spreadsheet experience.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
2. Start your SUMIFS formula: Click the target cell and enter your =SUMIFS() formula, selecting the sum and criteria ranges.
3. Combine wildcards and references: Use the & operator to link wildcard characters with your target cell reference, such as "*"&A1&"*".
4. Calculate dynamically: Press Enter to instantly view your dynamic calculation results, fully compatible with standard spreadsheet logic.

Fully compatible with Microsoft Excel formulas, functions, and .xlsx file formats.Supports advanced wildcards and dynamic array calculations out of the box.Lightweight software that processes large datasets quickly without lagging.Free to use with a familiar user interface, requiring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SUMIFS formula return 0 when I type "*A1*"?

Enclosing a cell reference entirely in quotation marks causes the spreadsheet program to treat it as literal text. The formula searches for the exact characters "A1" rather than the value stored inside cell A1. To fix this, you must separate the wildcards and link them to the reference using the ampersand operator, like "*"&A1&"*".

Can I use a cell reference with greater-than or less-than operators in SUMIFS?

Yes, you use the exact same concatenation method. To sum values that are greater than the value in cell B1, you must enter ">"&B1 as your criteria argument. Putting ">B1" inside quotes will fail to read the value in B1.

Does this concatenation method work for other functions like COUNTIF or VLOOKUP?

Absolutely. Any formula that accepts wildcard or operator criteria, such as COUNTIF, COUNTIFS, AVERAGEIF, and VLOOKUP, uses the exact same ampersand (&) concatenation logic to dynamically combine text strings with cell references.

Excel & Data Problems