The direct answer: use =SUMIF(range, "text", sum_range) to sum cells when a matching text value appears in another range. For example, =SUMIF(A2:A10, "Apples", B2:B10) adds all amounts in B2:B10 where column A contains exactly “Apples”.
How SUMIF Matches Text
SUMIF compares each cell in the criteria range against the text you supply. The comparison is case-insensitive by default, so “apples” and “APPLES” are treated the same. Text matches must be exact unless you use wildcards. A cell containing “Apple Pie” will not match the criteria “Apples” because SUMIF looks for an exact match unless you add wildcards.
The function has three arguments:
- range – the cells to check against the criteria (e.g., A2:A10).
- criteria – the text, number, or expression that defines which cells to sum (e.g., “Apples”).
- sum_range – the cells to add. If omitted, Excel sums the range itself.
Using Wildcards for Partial Matches
To match text that contains a certain word or phrase, use the asterisk (*) and question mark (?) wildcards.
- * matches any sequence of characters.
=SUMIF(A2:A10, "*Apple*", B2:B10)sums all rows where column A contains “Apple” anywhere. - ? matches any single character.
=SUMIF(A2:A10, "A?ple", B2:B10)matches “Apple” and “Aple” (if that existed) but not “Axxple”.
To match a literal asterisk or question mark, precede it with a tilde (~). For example, =SUMIF(A2:A10, "*~?*", B2:B10) sums rows where column A contains a question mark.
Summing with Cell References as Criteria
Instead of typing text directly, you can reference a cell that contains the text. This makes the formula dynamic. With the match text in D1, use:
=SUMIF(A2:A10, D1, B2:B10)
If you want to use a wildcard with a cell reference, concatenate the wildcard with the cell value using the ampersand (&). For example, to sum all rows where column A starts with the text in D1:
=SUMIF(A2:A10, D1&"*", B2:B10)
This is especially useful when you have a dropdown list in D1 and want the sum to update automatically.
Common Problems and Fixes
Several issues trip up users when using SUMIF with text:
- No match because of extra spaces: Text like “Apples ” (with a trailing space) will not match “Apples”. Use the TRIM function on the data or criteria. For example,
=SUMIF(A2:A10, TRIM(D1), B2:B10). - Case sensitivity: SUMIF is case-insensitive. If you need case-sensitive matching, use SUMPRODUCT with EXACT:
=SUMPRODUCT((EXACT(A2:A10,"Apples"))*B2:B10). - Numbers stored as text: If your criteria range contains numbers formatted as text, SUMIF may not match. Convert them to real numbers or use the VALUE function.
- Blank cells: To sum when a cell is blank, use
=SUMIF(A2:A10, "", B2:B10). To sum when not blank, use=SUMIF(A2:A10, "<>", B2:B10).
SUMIF vs. SUMIFS for Text
SUMIFS is the plural version that allows multiple criteria. The syntax is different: the sum range comes first, then pairs of criteria ranges and criteria. For text matching, SUMIFS works the same way with wildcards.
Example: sum sales in B2:B10 where column A is “Apples” and column C is “North”:
=SUMIFS(B2:B10, A2:A10, "Apples", C2:C10, "North")
SUMIFS is available in all modern Excel versions (Excel 2007 and later). For a single condition, SUMIF is simpler; for multiple, use SUMIFS.
Using SUMIF with Text in Google Sheets
Google Sheets uses the same SUMIF syntax. The only difference is that Google Sheets is case-insensitive as well, and wildcards work identically. However, Google Sheets does not require the tilde (~) for escaping wildcards; it uses a backslash (\). For example, to match a literal asterisk, use =SUMIF(A2:A10, "*\**", B2:B10).
Alternatives: SUMPRODUCT and FILTER
When you need more flexibility, consider these alternatives:
- SUMPRODUCT: Allows array operations and case-sensitive matching. Example:
=SUMPRODUCT((A2:A10="Apples")*B2:B10). - FILTER (Excel 365/2021): Returns an array of matching values, which you can then sum with SUM. Example:
=SUM(FILTER(B2:B10, A2:A10="Apples")). - COUNTIF: Counts matches instead of summing. Use
=COUNTIF(A2:A10, "Apples")to count how many times “Apples” appears.
FILTER is more flexible because you can use it with other functions, but it requires Excel 2021 or Microsoft 365.
Frequently Asked Questions
Can I use SUMIF to match text that contains a specific word? Yes, use wildcards. For example, =SUMIF(A2:A10, "*apple*", B2:B10) sums all rows where column A contains “apple” anywhere.
Why is my SUMIF returning 0 even though the text looks identical? Check for invisible characters like extra spaces or non-breaking spaces. Use the TRIM function on the criteria or the data. Also, ensure the criteria range is formatted as text, not as a number.
How do I make SUMIF case-sensitive? SUMIF cannot do case-sensitive matching. Use SUMPRODUCT with EXACT: =SUMPRODUCT((EXACT(A2:A10,"Apples"))*B2:B10).
Can I use a cell reference for the text criteria without typing quotes? Yes, simply reference the cell: =SUMIF(A2:A10, D1, B2:B10). For partial matches, concatenate: =SUMIF(A2:A10, "*"&D1&"*", B2:B10).
Does SUMIF work with text that has leading or trailing spaces? Only if the spaces are part of the criteria. Use TRIM to remove them from the data or the criteria. For example, =SUMIF(A2:A10, TRIM(D1), B2:B10).
What is the difference between SUMIF and SUMIFS for text? SUMIF is for a single condition; SUMIFS is for multiple conditions. SUMIFS has the sum range first, then pairs of criteria ranges and criteria. Both support wildcards.
Related
- Excel Power Query Split Data: How-To Guide
- Merge Two Tables in Excel Power Query: Full Guide
- Excel Power Query Prevent Refresh: How to Stop It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Delete Table but Keep Data: The Right StepsTo delete an Excel table but keep its data, convert the table to a normal range: click a…
- How to Calculate Quality Percentage in Excel With FormulasTo calculate quality percentage in Excel, divide the number of checks that passed by the total number of…
- Excel Formula for Checking Account? Track BalancesThe quickest formula to track a checking account in Excel is a running balance: with your starting balance…
- Excel COUNTIFS Not Equal to Text: Formula ExamplesTo count cells that are not equal to specific text, use COUNTIFS with the not-equal operator inside quotation…
- FLOOR Excel Formula vs. ROUNDDOWN: Key DifferencesIn Excel, FLOOR rounds a number down to a chosen multiple, while ROUNDDOWN cuts off digits at a…
- Excel Sort Dates From Earliest to Latest: A Clear GuideTo sort dates in Excel from earliest to latest, select a cell in your date column, then choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.