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
- Excel Power Query Merge 3 Queries: Step-by-Step
- Excel Power Query Incremental Refresh: How to Set It Up
- Excel Power Query: Append to Existing Table
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Quarterly Payments: How to CalculateThe simplest Excel formula for a quarterly payment is =PMT(rate, nper, pv), where rate is the quarterly interest…
- Excel FILTER Function for Text: Examples and TipsWith records in A2:C6 and the text to match in B2:B6, use =FILTER(A2:C6, B2:B6="North", "No matching rows") to…
- Excel Formula for Week Start Date: Monday or SundayTo return the Monday of the week containing a date in A2, use =A2-WEEKDAY(A2,2)+1. To return Sunday instead,…
- How to Filter Multiple Values in Excel: A Practical GuideTo filter for several values in one Excel column, open its filter dropdown, clear (Select All), check each…
- Excel MIN IF: Find the Smallest Value That Meets a TestTo find the smallest value in Excel that meets a condition, use MINIFS in Microsoft 365: with regions…
- What Column Number Is H in Excel? Column Index ExplainedIn Excel, column H is column number 8. The letters A through H count upward from 1, so…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.