Excel SUMIF Contains Text From Another Cell?

Formulas & functions · Updated November 22, 2026

Yes, you can use the SUMIF function to sum values when a corresponding cell contains text from another cell by using wildcards and concatenation. The formula is =SUMIF(range, "*"&cell&"*", sum_range) — for example, =SUMIF(A2:A10, "*"&D1&"*", B2:B10) sums all values in B2:B10 where the text in A2:A10 contains the text in D1.

How SUMIF Works With Text Criteria

SUMIF takes three arguments: the range to check, the criteria, and the range to sum. When the criteria is text, Excel looks for an exact match unless you use wildcards. The asterisk (*) matches any sequence of characters, and the question mark (?) matches any single character. By concatenating an asterisk before and after the cell reference, you tell Excel to match any cell that contains that text anywhere within it.

For example, with product names in A2:A10 and sales in B2:B10, to sum sales for any product containing “apple” (where “apple” is typed in D1), use:

=SUMIF(A2:A10, "*"&D1&"*", B2:B10)

This works because "*"&D1&"*" becomes the text *apple*, which Excel interprets as a wildcard pattern.

Using a Cell Reference as the Search Text

The key is the ampersand (&) operator, which joins text. Without it, writing "*D1*" would look for the literal characters D and 1, not the value in D1. Always use "*"&D1&"*" to build the pattern dynamically.

If the cell contains numbers (e.g., a product code like 123), Excel will still treat it as text in the criteria, so the formula works the same way. Just ensure the cell is formatted as text or the number is stored as text, otherwise the concatenation still converts it to text automatically.

Case Sensitivity and Exact Matches

SUMIF is not case-sensitive. It treats “Apple” and “apple” as the same. If you need case-sensitive matching, you must use SUMPRODUCT with ISNUMBER and FIND, like this:

=SUMPRODUCT(--ISNUMBER(FIND(D1, A2:A10)), B2:B10)

This formula checks each cell in A2:A10 for the exact text in D1, respecting case, and sums the corresponding B values. Note that FIND returns an error if not found, so ISNUMBER converts that to TRUE/FALSE, and the double unary (–) converts TRUE/FALSE to 1/0.

Common Problems and Fixes

  • No matches returned: Check that the text in the criteria cell has no extra spaces. Use TRIM on the cell or the criteria: "*"&TRIM(D1)&"*".
  • Unexpected matches: The asterisk wildcard matches any characters, so a search for “cat” will also match “category”. If you need a whole-word match, use a more complex formula like SUMPRODUCT with word boundaries, but that’s beyond SUMIF.
  • Numbers stored as text: If your criteria cell contains a number but the range contains numbers stored as text, the match may fail. Convert the criteria to text with TEXT(D1, “0”) if needed.
  • Wildcard characters in the search text: If the cell contains an actual asterisk or question mark, Excel treats it as a wildcard. To match a literal asterisk, use a tilde (~) before it: "*"&SUBSTITUTE(D1,"*","~*")&"*".

Alternatives: SUMIFS, SUMPRODUCT, and FILTER

SUMIFS is the newer version that allows multiple criteria, but it works the same way with wildcards. For example, to sum sales where the product contains “apple” and the region is “West”:

=SUMIFS(B2:B10, A2:A10, "*"&D1&"*", C2:C10, "West")

If you need to sum based on a partial match and also return other calculations, SUMPRODUCT is more flexible. For Microsoft 365, you can also use FILTER to extract the matching rows and then SUM, like:

=SUM(FILTER(B2:B10, ISNUMBER(SEARCH(D1, A2:A10))))

SEARCH is not case-sensitive and also supports wildcards, but here it’s used without them to check for the presence of the text.

Performance and Large Data Sets

SUMIF with wildcards is generally fast, but on very large datasets (hundreds of thousands of rows), it may slow down. SUMPRODUCT and FILTER can also be heavy. For best performance, consider using a helper column with a formula like =ISNUMBER(SEARCH($D$1, A2)) and then SUMIF on that helper column with TRUE as the criteria. This avoids repeated wildcard evaluations.

Frequently Asked Questions

Can I use a cell reference for the sum range?

Yes, the sum_range can be a cell reference or a range. For example, =SUMIF(A2:A10, "*"&D1&"*", B2:B10) sums B2:B10. If you omit sum_range, SUMIF sums the range itself.

How do I sum if the cell does NOT contain text?

Use the not-equal-to operator with wildcards: =SUMIF(A2:A10, "<>*"&D1&"*", B2:B10). This sums cells that do not contain the text in D1.

What if my search text is in a merged cell?

Merged cells can cause issues because only the top-left cell holds the value. Reference the top-left cell of the merged range (e.g., D1 if D1:D3 is merged). The formula will work, but be aware that the criteria only sees that one cell’s value.

Can I use a wildcard directly in the cell, like “*apple*”?

Yes, you can type *apple* directly into the criteria cell and then use =SUMIF(A2:A10, D1, B2:B10). However, if you later change D1 to just “apple”, the formula will look for an exact match. Using concatenation with asterisks in the formula is more flexible.

Does SUMIF work with dates?

Yes, but dates are stored as numbers. If you want to sum cells where the date contains a specific month, you’d need to use SUMPRODUCT with MONTH, or convert the date to text. For example, =SUMPRODUCT((TEXT(A2:A10,"mmm")=D1)*B2:B10) sums where the month abbreviation matches D1.

Final Tips for Reliable Results

Always test your formula on a small sample first. Remember that SUMIF ignores blank cells in the range. If your criteria cell is empty, the formula "*"&""&"*" becomes **, which matches all non-blank cells, so you may want to add an IF condition to handle empty criteria, like =IF(D1="",0,SUMIF(...)).

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.