Excel SUMIF Contains Specific Text: Formula & Examples

Formulas & functions · Updated November 22, 2026

To sum values in Excel when a cell contains specific text, use SUMIF with wildcards: =SUMIF(range,"*text*",sum_range). For example, =SUMIF(A2:A10,"*apple*",B2:B10) sums all amounts in B2:B10 where the corresponding cell in A2:A10 contains the word “apple” anywhere in the text.

How SUMIF Works with Text Criteria

SUMIF has three arguments: range (the cells to check), criteria (the condition), and sum_range (the cells to add). When the criteria is text, Excel matches exactly unless you use wildcards. The asterisk (*) matches any sequence of characters, and the question mark (?) matches any single character.

For “contains” logic, place an asterisk before and after the text: "*text*". This tells Excel to match any cell that has that text anywhere, not just at the start or end. Without the trailing asterisk, "*text" matches text that ends with “text”; with only a leading asterisk, "text*" matches text that starts with “text”.

Example: with product names in A2:A20 and sales in B2:B20, to sum sales for any product that includes “red” (like “red apple” or “small red box”), use:

=SUMIF(A2:A20,"*red*",B2:B20)

If the data contains “Red” with a capital R, the match is case-insensitive by default, so it still works. If you need case-sensitive matching, you must use SUMPRODUCT with ISNUMBER and FIND (see the alternatives section).

Using Cell References in the Criteria

Hardcoding text in the formula is fine for one-off tasks, but when you want to change the search term without editing the formula, put the text in a cell and concatenate the wildcards. Suppose cell D1 contains “apple”. Then the formula becomes:

=SUMIF(A2:A20,"*"&D1&"*",B2:B20)

The ampersand (&) joins the asterisks with the cell content. This works in all Excel versions. If the cell contains a wildcard character itself (like * or ?), Excel treats it as a wildcard, not as a literal character. To match a literal asterisk, you need to escape it with a tilde (~), for example "*~**" matches any text containing an asterisk.

Common Problems and Fixes

  • No match because of extra spaces: If cells have leading or trailing spaces, “*text*” won’t match. Use =SUMIF(A2:A20,"*"&TRIM(D1)&"*",B2:B20) or clean the data first.
  • Numbers stored as text: If the sum_range contains numbers stored as text, SUMIF returns 0. Convert them to numbers using the error-checking button or by multiplying by 1.
  • Wildcard characters in the search term: If you search for a literal question mark or asterisk, prefix it with a tilde (~). For example, to sum values for cells containing “5%?”, use "*5%~?*".
  • Case sensitivity: SUMIF is not case-sensitive. If you need to distinguish “Apple” from “apple”, use SUMPRODUCT with FIND.
  • Whole-word match: The wildcard method matches substrings, so “apple” also matches “pineapple”. If you only want whole words, you’ll need a more complex array formula or a helper column.

Alternatives: SUMIFS, SUMPRODUCT, and FILTER

SUMIFS is the plural version and allows multiple criteria. To sum with a contains condition plus another condition (e.g., region = “West”):

=SUMIFS(B2:B20,A2:A20,"*red*",C2:C20,"West")

Note that in SUMIFS, the sum_range comes first, unlike SUMIF.

For case-sensitive contains, use SUMPRODUCT with ISNUMBER and FIND. With text in A2:A20 and values in B2:B20, and the search term in D1:

=SUMPRODUCT(--(ISNUMBER(FIND(D1,A2:A20))),B2:B20)

FIND is case-sensitive and returns a number if the text is found, otherwise an error. The double unary (–) converts TRUE/FALSE to 1/0. This works in all Excel versions.

If you have Excel 365, you can use FILTER and SUM:

=SUM(FILTER(B2:B20,ISNUMBER(SEARCH(D1,A2:A20))))

SEARCH is case-insensitive; use FIND for case-sensitive. FILTER requires Excel 365 or Excel 2021.

How to Choose the Right Method

  • Use SUMIF for a single contains condition and you don’t need case sensitivity.
  • Use SUMIFS when you have additional criteria (dates, other text, numbers).
  • Use SUMPRODUCT for case-sensitive matching or when you need to perform calculations on the criteria (e.g., sum values where the text length is >3).
  • Use FILTER + SUM if you have Excel 365 and want a dynamic array that can be further processed.
  • For very large datasets (100,000+ rows), SUMIFS and SUMIF are faster than SUMPRODUCT and FILTER.

Real-World Example: Sales by Product Category

Assume a sales table with columns: A = Product, B = Amount. Data rows 2 to 100. You want total sales for any product that contains “phone” (e.g., “smartphone”, “phone case”). Place the search term “phone” in D1, then use:

=SUMIF(A2:A100,"*"&D1&"*",B2:B100)

If the result is 0, check for spaces or inconsistent capitalization. Also note that SUMIF ignores blank cells in the range, but if the criteria cell is blank, the formula returns 0 because “**” matches any non-empty cell? Actually, “**” matches any text, so if D1 is empty, the formula becomes "**" which sums all non-blank text cells. To avoid that, add an IF condition: =IF(D1="","",SUMIF(...)).

Frequently Asked Questions

Can SUMIF match text that appears at the start only?

Yes, use "text*" (asterisk at the end) to match cells that begin with “text”. For example, =SUMIF(A2:A20,"apple*",B2:B20) sums values where the cell starts with “apple”.

How do I sum if a cell contains one of several text strings?

You can add multiple SUMIF formulas: =SUMIF(A2:A20,"*apple*",B2:B20)+SUMIF(A2:A20,"*banana*",B2:B20). Or use SUMPRODUCT with an array of criteria: =SUMPRODUCT(SUMIF(A2:A20,{"*apple*","*banana*"},B2:B20)). Note that the array constant uses curly braces and commas.

Why does my SUMIF return 0 even though the text is there?

Common causes: extra spaces in the data or criteria, numbers stored as text, or the criteria cell contains a wildcard character that isn’t escaped. Also, if the range includes blank cells, they are ignored, but if the criteria is a blank cell, it may match all non-blank cells. Check with TRIM and use the formula evaluator.

Does SUMIF work with partial text in a different column?

Yes, the range and sum_range can be different columns. The criteria is applied to the range, and the corresponding cells in sum_range are summed. For example, =SUMIF(A2:A20,"*red*",C2:C20) sums column C where column A contains “red”.

Is there a way to sum if a cell does NOT contain specific text?

Yes, use the not-equal-to operator with wildcards: =SUMIF(A2:A20,"<>*text*",B2:B20). This sums all cells that do not contain “text”. Note that this also includes blank cells, because a blank cell does not contain the text. If you want to exclude blanks, use =SUMIFS(B2:B20,A2:A20,"<>*text*",A2:A20,"<>").

Can I use a cell reference for the sum_range in SUMIF?

Yes, you can use any valid range reference, including named ranges. For example, if you define a named range “Amounts” for B2:B20, you can write =SUMIF(A2:A20,"*apple*",Amounts).

Tips for Large Datasets and Performance

SUMIF and SUMIFS are optimized for speed, but using wildcards with leading asterisks prevents Excel from using any index, so performance may degrade on very large datasets. If you have hundreds of thousands of rows, consider adding a helper column that flags whether the text contains the term (using ISNUMBER(SEARCH(…))), then use SUMIF on that flag column. Also, avoid using full-column references (e.g., A:A) in SUMIF with wildcards, as that forces Excel to scan the entire column; instead, limit the range to the actual data area.

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.