Excel SUMIF Contains Partial Text: Formula & Examples

Formulas & functions · Updated November 22, 2026

Yes, you can use SUMIF for partial text matches in Excel by combining it with wildcard characters. The formula is =SUMIF(range, "*text*", sum_range), where the asterisks (*) act as wildcards that match any sequence of characters before or after your search term. For example, to sum all sales in cells B2:B10 where the product name in A2:A10 contains “apple”, you would use =SUMIF(A2:A10, "*apple*", B2:B10).

How Wildcards Work in SUMIF

Excel’s SUMIF function supports three wildcard characters, but only two are useful for partial text matching:

  • Asterisk (*) – matches any number of characters (including zero). Placing *text* finds cells containing “text” anywhere.
  • Question mark (?) – matches exactly one character. For example, "A?C" matches “ABC”, “A1C”, but not “AC” or “ABBC”.
  • Tilde (~) – escapes the next character, so "~*" matches a literal asterisk, not a wildcard.

These wildcards work in SUMIF, COUNTIF, AVERAGEIF, and their plural counterparts (SUMIFS, COUNTIFS, etc.). They do not work in functions like VLOOKUP by default, though you can use wildcards there with a workaround.

Basic SUMIF with Partial Text: Step-by-Step

Assume your data has product names in column A (A2:A20) and sales amounts in column B (B2:B20). To sum all sales for products that contain the word “red”:

  1. Click an empty cell, for example D2.
  2. Type =SUMIF(A2:A20, "*red*", B2:B20).
  3. Press Enter. The result will be the total of all sales where the product name includes “red” anywhere—e.g., “red apple”, “strawberry red”, “red velvet cake”.

If your data has 100 rows and you want to reference a cell containing the search term (say C1), use =SUMIF(A2:A100, "*"&C1&"*", B2:B100). The ampersand (&) concatenates the asterisks with the cell value.

Using SUMIFS for Multiple Partial Text Criteria

When you need to match partial text in more than one column, use SUMIFS. The syntax is =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...).

Example: Sum sales in B2:B20 where column A contains “red” AND column C contains “large”. The formula is:

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

Both conditions must be true for a row to be included. Wildcards work exactly the same way in SUMIFS as in SUMIF.

Case Sensitivity and Partial Text

SUMIF and SUMIFS are not case-sensitive. "*APPLE*" will match “Apple”, “apple”, and “APPLE”. If you need case-sensitive matching, you must use SUMPRODUCT with the ISNUMBER and FIND functions, because FIND is case-sensitive while SEARCH is not.

For case-sensitive partial text sums, use this array-like formula (no Ctrl+Shift+Enter needed in modern Excel):

=SUMPRODUCT((ISNUMBER(FIND("apple", A2:A20)))*B2:B20)

This checks each cell in A2:A20 for the exact text “apple” (case-sensitive) and multiplies the TRUE/FALSE result by the corresponding sales amount, then sums the products.

Common Mistakes and How to Avoid Them

  • Forgetting the asterisks – "apple" without asterisks matches only cells that are exactly “apple”, not those containing it.
  • Using the wrong range size – The criteria range and sum range must be the same size, or you’ll get a #VALUE! error.
  • Mixing wildcards with cells containing literal asterisks – If your data has an asterisk as a character, use a tilde: "~*" to match it literally.
  • Assuming wildcards work in all functions – They don’t work in XLOOKUP or VLOOKUP by default, though you can enable them in VLOOKUP by setting the last argument to TRUE (approximate match) which is not recommended.

Alternatives: SUMPRODUCT, FILTER, and Dynamic Arrays

SUMIF is the simplest solution, but you have other options depending on your Excel version:

  • SUMPRODUCT with SEARCH – For case-insensitive partial text, use =SUMPRODUCT((ISNUMBER(SEARCH("apple", A2:A20)))*B2:B20). SEARCH is case-insensitive and allows wildcards.
  • FILTER (Excel 2021 or Microsoft 365) – Use =SUM(FILTER(B2:B20, ISNUMBER(SEARCH("apple", A2:A20)))). This is more flexible if you need to return the filtered rows themselves.
  • SUMIFS with multiple criteria – As shown above, it’s the best for multiple conditions.

If you’re using Excel for the web or Mac, the functions are identical. The only difference is keyboard shortcuts (e.g., Ctrl+Enter on Windows vs Cmd+Enter on Mac) but formulas work the same.

Frequently Asked Questions

Can I use SUMIF with partial text and a cell reference for the text?
Yes, concatenate the wildcards with the cell reference: =SUMIF(A2:A20, "*"&C1&"*", B2:B20). This works in all versions.

Why does my SUMIF return 0 even though I see matching text?
Check for leading/trailing spaces in your data. Use =TRIM(A2) to clean them, or use wildcards that account for spaces: "* text *". Also ensure your sum range is numeric—if it’s text-formatted numbers, SUMIF will ignore them.

Does SUMIF work with partial text in Google Sheets?
Yes, the syntax is identical. Google Sheets supports the same wildcards (* and ?) in SUMIF and SUMIFS. The only difference is that in Google Sheets, you might use =SUMIF(A2:A, "*text*", B2:B) with open-ended ranges, which Excel does not allow.

How do I sum if a cell starts with a specific text?
Use "text*" (no leading asterisk) to match cells that start with “text”. For ends with, use "*text". For contains, use "*text*".

Can I use wildcards in SUMIFS for dates or numbers?
Wildcards only work with text. For numbers, you must use comparison operators like ">100" or ">=01/01/2023". For dates, use the DATE function: =SUMIFS(B2:B20, A2:A20, ">="&DATE(2023,1,1)).

Is there a limit to how many wildcards I can use in one formula?
No, but keep in mind that each wildcard adds complexity. Excel can handle many, but performance may slow on very large datasets (100,000+ rows). In such cases, consider using Power Query or a helper column with ISNUMBER(SEARCH()).

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.