Excel SUMIF Includes Text? How It Works

Formulas & functions · Updated November 22, 2026

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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.