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”:
- Click an empty cell, for example D2.
- Type
=SUMIF(A2:A20, "*red*", B2:B20). - 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
- Excel Sales Tracking Template by Product: How to Build One
- Excel Reverse Mortgage Calculator Spreadsheet
- Excel Reverse Lookup with Multiple Criteria: How-To
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Rank No Ties? Use RANK.EQ and COUNTIFYes, Excel’s RANK.EQ function assigns the same rank to tied values by default, so two identical scores both…
- Excel DGET vs INDEX MATCH: Which Should You Use?For a normal lookup by one key, INDEX MATCH is usually the straightforward choice; DGET is useful when…
- Match Names on Two Excel Sheets with VLOOKUPYes. VLOOKUP can find a name on one Excel sheet in a second sheet and return a related…
- Excel Date Filter Multiple Criteria: Practical MethodsTo filter dates in Excel by multiple criteria, turn on AutoFilter, open the date column’s dropdown, and choose…
- How to Divide in Excel: Formulas and ExamplesTo divide in Excel, enter a formula with a forward slash, such as =A2/B2, and press Enter. Replace…
- Sort Excel Numbers in Order: Steps, Formulas, and FixesTo sort numbers in Excel, select a cell in the number list and choose Data > Sort &…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.