Yes, Excel’s SUMIF function can include text, but not as a value to add—text is used as a criterion to decide which numbers to sum. The function adds up numbers in a range only when a corresponding cell in another range matches a text condition, such as "Apples" or "*Apples*". If you try to sum cells that contain text, SUMIF returns 0 because it only adds numeric values; text cells are ignored.
What SUMIF Actually Does
SUMIF is designed to add numbers based on a condition. Its syntax is:
=SUMIF(range, criteria, [sum_range])
- range: the cells you check against the criteria.
- criteria: the condition that determines which cells to sum—this can be a number, text, expression, or cell reference.
- sum_range (optional): the cells to add. If omitted, Excel sums the cells in range itself.
For example, with a product list in A2:A10 and sales in B2:B10, the formula =SUMIF(A2:A10, "Apples", B2:B10) adds all sales where column A says “Apples”. The text “Apples” is the criterion, and it works exactly as you’d expect.
How Text Criteria Work in SUMIF
Text criteria are written inside double quotes. You can use exact matches or wildcards:
- Exact match:
"Apples"matches cells that contain only “Apples” (case-insensitive). - Wildcard *:
"*Apples*"matches any cell containing “Apples” anywhere (e.g., “Green Apples”, “Applesauce”). - Wildcard ?:
"A?ple"matches “Apple” or “Aple” (each ? represents one character). - Not equal:
"<>Apples"sums all cells that are not exactly “Apples”.
If your text contains an actual asterisk or question mark, you need to escape it with a tilde (~) before it, like "~*" to match a literal asterisk.
Can SUMIF Sum Text Values Directly?
No. SUMIF only adds numbers. If your sum_range contains text, those cells are ignored. For instance, if B2:B5 contains {“10”, “20”, “thirty”, “40”} (where “thirty” is text), a SUMIF that matches all rows will return 70, not 100. This is a common misconception: people expect SUMIF to convert text numbers, but it doesn’t. To sum numbers stored as text, you need to convert them first (e.g., using the VALUE function or Text to Columns) or use SUMPRODUCT with a coercion trick.
Common Problems and Solutions
Here are frequent issues users run into:
- Criteria from a cell reference: If you put the text in a cell (say D1), use
=SUMIF(A2:A10, D1, B2:B10)—no quotes needed around the cell reference. - Leading/trailing spaces: Text like ” Apples ” won’t match “Apples”. Use TRIM on the data or the criteria.
- Case sensitivity: SUMIF is not case-sensitive; “apples” and “APPLES” match. For case-sensitive matching, use SUMPRODUCT with EXACT.
- Using > or < with text: You can use comparison operators with text (e.g.,
">m"sums cells alphabetically after “m”), but this is rarely useful and can be confusing. - SUMIF with text in sum_range: As noted, text in sum_range is ignored. If you need to sum numbers stored as text, use
=SUMPRODUCT((A2:A10="Apples")*(B2:B10))but that requires B2:B10 to be true numbers, not text.
SUMIF vs. SUMIFS: Which One to Use?
SUMIFS is the newer function that handles multiple criteria, and it has a different argument order: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). For a single text criterion, both work, but SUMIFS is more flexible and is recommended for new work. For example, to sum sales for “Apples” in the West region, you’d use =SUMIFS(B2:B10, A2:A10, "Apples", C2:C10, "West"). SUMIFS also ignores text in the sum_range the same way.
Using Wildcards and Special Characters
Wildcards are powerful but can cause unexpected matches. For instance, "*" matches any text, so =SUMIF(A2:A10, "*", B2:B10) sums all rows where column A is not empty (text or numbers). To match a literal asterisk, use "~*". Similarly, a question mark is a wildcard, so to match a literal question mark, use "~?". This is essential when your data contains product codes like “A*1” or “Q?2”.
Frequently Asked Questions
Does SUMIF work with text that looks like numbers?
No, if the criterion is text “10”, it will only match cells that are text, not numbers. SUMIF treats numbers and text differently. To match both, use a numeric criterion (10) or convert text to numbers.
Can I use SUMIF to count text cells?
No, SUMIF sums numbers. To count cells that contain specific text, use COUNTIF. For example, =COUNTIF(A2:A10, "Apples") counts how many times “Apples” appears.
Why does my SUMIF return 0 when I know there are matches?
Check for invisible characters like spaces, or that the sum_range is actually numeric. Also ensure the criteria is in quotes if it’s text. If you reference a cell, make sure it doesn’t have extra spaces.
Is there a way to sum text values as numbers?
If your data has numbers stored as text, you can use =SUMPRODUCT((A2:A10="Apples")*(VALUE(B2:B10))) but this requires B2:B10 to be text that can be converted. Alternatively, use Text to Columns to convert them to real numbers first.
Can I use SUMIF with partial text match?
Yes, use wildcards. For example, =SUMIF(A2:A10, "*Apples*", B2:B10) sums all rows where column A contains “Apples” anywhere.
Alternatives When SUMIF Falls Short
If you need case-sensitive matching, multiple criteria, or to sum based on text in the sum_range, consider:
- SUMPRODUCT:
=SUMPRODUCT((A2:A10="Apples")*(B2:B10))works with arrays and can handle more complex logic, including case-sensitive with EXACT. - FILTER (Excel 365):
=SUM(FILTER(B2:B10, A2:A10="Apples"))returns an array of matching values, which you can sum. - SUMIFS for multiple conditions, as mentioned.
For most text-criterion needs, SUMIF is perfectly adequate. Just remember: text is for the condition, not the sum.
Related
- Excel Power Query: Get Data From Folder
- Excel: Count Cells with Specific Text
- Excel Online Schedule Template: How to Use and Find
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Find Unique Names in Excel: 3 Reliable MethodsTo find a list of distinct names in Excel, enter =UNIQUE(A2:A100) in an empty cell if you use…
- Excel Conditional Formatting With a Custom FormulaTo use a custom formula in Excel conditional formatting, select the cells to format, then choose Home >…
- Function in CodeHS: Meaning, Syntax, and ExamplesA function in CodeHS is a named, reusable block of code that performs a task. It can receive…
- Excel Growth Rate Formula: Calculate Change Over TimeTo calculate growth rate in Excel, divide the change between the new and old values by the old…
- Python Functions: How They Work and How to Write OnePython has no special built-in function named X: function names are chosen by the person writing the program.…
- COUNTIFS in Excel: Exact and Partial Text MatchesYes. In Excel, COUNTIFS can count cells that match text exactly or contain a text fragment: use =COUNTIFS(A2:A100,"Apple")…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.