The Excel SUMIF function can sum values based on a text criterion, such as a specific word, a partial match, or even cells that are blank or non-blank. The basic syntax is =SUMIF(range, "criteria", sum_range), where the criteria is a text string enclosed in double quotes. For example, =SUMIF(A2:A10, "Apples", B2:B10) sums all values in B2:B10 where the corresponding cell in A2:A10 equals “Apples”.
How SUMIF Works with Text Criteria
SUMIF evaluates each cell in the range against the criteria and adds the corresponding value from sum_range. Text criteria are not case-sensitive, so “apples” and “APPLES” match the same cells. The criteria can be a direct text string, a cell reference containing text, or a wildcard pattern.
Key points to remember:
- The
rangeandsum_rangemust be the same size, but they do not have to be in the same columns. - If you omit
sum_range, SUMIF sums the cells inrangeitself. - Text criteria must be enclosed in double quotes, except when using a cell reference (e.g.,
=SUMIF(A2:A10, D1, B2:B10)where D1 contains the text).
Using Wildcards for Partial Matches
Wildcards let you match text that contains, starts with, or ends with specific characters. The asterisk (*) matches any sequence of characters, and the question mark (?) matches any single character. For example:
=SUMIF(A2:A10, "*Apple*", B2:B10)sums values for any cell containing “Apple” anywhere in the text.=SUMIF(A2:A10, "A*", B2:B10)sums values for cells starting with “A”.=SUMIF(A2:A10, "???", B2:B10)sums values for cells that are exactly three characters long.
To match a literal asterisk or question mark, use a tilde (~) before it, like "~*".
Summing Cells That Are Blank or Non-Blank
You can also use SUMIF to sum cells based on whether the corresponding cell is empty or not. Use the criteria "=" to sum cells that are blank, and "<>" to sum cells that are not blank. For example:
=SUMIF(A2:A10, "=", B2:B10)sums B values where A is blank.=SUMIF(A2:A10, "<>", B2:B10)sums B values where A is not blank.
Note that cells containing empty strings (e.g., from formulas like ="") are treated as text, not as truly blank, and will not match the “=” criteria.
Common Problems and Solutions
Several issues commonly trip up users when using SUMIF with text:
- Criteria not matching due to extra spaces: Text with leading or trailing spaces will not match. Use
=TRIM(A2)to clean data, or use wildcards like"*Apple*"to ignore surrounding spaces. - Numbers stored as text: If your sum_range contains numbers stored as text, SUMIF may return 0. Convert them to numbers using the VALUE function or Text to Columns.
- Case sensitivity: SUMIF is not case-sensitive. If you need case-sensitive matching, use SUMPRODUCT with EXACT, e.g.,
=SUMPRODUCT((EXACT(A2:A10,"Apple")*B2:B10)). - Wildcard characters in data: If your data contains literal asterisks or question marks, they are treated as wildcards. Escape them with a tilde in the criteria.
SUMIF vs. SUMIFS for Multiple Criteria
If you need to sum based on multiple text criteria, use SUMIFS. The syntax is =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). For example, to sum sales where product is “Apples” and region is “North”, use:
=SUMIFS(C2:C10, A2:A10, "Apples", B2:B10, "North")
SUMIFS also supports wildcards and cell references. Note that SUMIFS requires the sum_range to be the first argument, unlike SUMIF.
Using Cell References in Criteria
Instead of hardcoding text in the formula, you can reference a cell that contains the text. This makes the formula dynamic. For example, if D1 contains “Apples”, use =SUMIF(A2:A10, D1, B2:B10). When using a cell reference, do not enclose the reference in quotes. To concatenate a cell reference with wildcards, use the ampersand (&), like =SUMIF(A2:A10, "*"&D1&"*", B2:B10).
Frequently Asked Questions
Can SUMIF sum text values themselves?
No, SUMIF adds numeric values. If your sum_range contains text, SUMIF returns 0. To count text cells, use COUNTIF or COUNTA. To concatenate text based on criteria, you would need a more advanced formula or a pivot table.
Why does my SUMIF return 0 even though the text matches?
This usually happens when the sum_range contains numbers stored as text, or when the criteria text has invisible characters like non-breaking spaces. Check the data type of the sum_range cells (should be General or Number) and use TRIM or CLEAN to remove hidden characters.
How do I sum cells that do NOT contain specific text?
Use the not-equal operator with wildcards: =SUMIF(A2:A10, "<>*Apple*", B2:B10). This sums values where the corresponding cell does not contain “Apple”.
Is SUMIF case-sensitive?
No, SUMIF is not case-sensitive. For case-sensitive text matching, use SUMPRODUCT with EXACT as shown earlier.
Can I use SUMIF with a drop-down list?
Yes. If your criteria cell (e.g., D1) is a data validation drop-down, your SUMIF formula will automatically update when you change the selection, because it references that cell.
Alternatives to SUMIF for Text Criteria
If SUMIF doesn’t meet your needs, consider these alternatives:
- SUMIFS: For multiple criteria, including text and numeric.
- SUMPRODUCT: For complex conditions, case-sensitive matching, or when you need to sum based on multiple columns without adding helper columns.
- FILTER function: In Excel 365, you can use
=SUM(FILTER(B2:B10, A2:A10="Apples"))to filter and sum in one step. - PivotTables: For quick grouping and summing by text categories without formulas.
Each method has its strengths, but SUMIF remains the simplest for a single text condition.
Related
- Excel Month of Date as Text: 5 Easy Ways
- Free Excel Mileage Log Templates: 7 Real Options
- Excel Lowercase: Convert Text to Lowercase
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Wildcard SUMIF Formula Examples That Match TextTo total values for text that contains a word, use SUMIF with asterisks around the word: with product…
- Today’s Date in Excel: Number, Formula, and MeaningIn Microsoft Excel for Microsoft 365 on Windows, today’s date, November 3, 2026, is serial number 46329 in…
- Excel Sort and Filter Shortcuts: A Practical GuideIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn filter buttons on or off for a…
- How to Compare Records in Two Excel Sheets with FormulasTo compare records in two Excel sheets, match each row by a shared, unique ID rather than by…
- How to Delete a Formula in Excel but Keep Its ValueTo delete a formula but keep its calculated result in Excel, select the formula cells, copy them, then…
- Excel MID Function: Extract Text from the Middle of a CellExcel’s MID function extracts a chosen number of characters from the middle of a text string. Its syntax…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.