Excel SUMIF When Range Contains Text

Formulas & functions · Updated November 22, 2026

Yes, you can use SUMIF when the range contains text, but the text is used as a criteria to filter which numbers to sum, not as the values being summed. The standard syntax is =SUMIF(range, criteria, [sum_range]), where the first range can contain text labels, and Excel matches those labels against your criteria to decide which cells in the sum_range to add.

How SUMIF Handles Text Criteria

SUMIF is designed to check a range of cells (which may contain text, numbers, or blanks) against a condition, then sum corresponding cells in another range. When your criteria is text, Excel looks for exact matches by default, unless you use wildcards. For example, with product names in A2:A10 and sales in B2:B10, the formula =SUMIF(A2:A10, "Apples", B2:B10) adds only the sales values where column A equals “Apples” exactly. Text criteria are not case-sensitive, so “apples” and “APPLES” both match.

If you want to sum based on partial text, use wildcards: * matches any sequence of characters, and ? matches a single character. For instance, =SUMIF(A2:A10, "App*", B2:B10) sums sales for any product starting with “App”. To match text that literally contains an asterisk or question mark, precede it with a tilde (~), like "~*".

When the Sum Range Itself Contains Text

A common confusion: what if the cells you want to sum contain text? SUMIF ignores text in the sum_range—it only adds numeric values. If your sum_range has cells with text like “N/A” or “-“, those cells are treated as zero and skipped. For example, with B2:B10 containing numbers and text, =SUMIF(A2:A10, "Apples", B2:B10) will sum only the numeric entries in B that correspond to “Apples” in A. If all matching cells contain text, the result is 0.

If you need to sum numbers that are stored as text (e.g., “100” as a string), SUMIF will not add them. To handle that, convert text to numbers using the VALUE function or by multiplying by 1, or use SUMPRODUCT with a coercion trick like =SUMPRODUCT((A2:A10="Apples")*(B2:B10+0)).

Using SUMIF with Text in the Same Range

Sometimes you want to sum values that are in the same column as the text criteria. For example, you have a column with alternating labels and numbers, like “Apples” in A2, 100 in A3, “Oranges” in A4, 50 in A5. To sum all numbers that follow a specific label, you need a different approach because SUMIF expects separate ranges. A simple SUMIF won’t work directly. Instead, use SUMPRODUCT with OFFSET or use a helper column. A helper column in B could contain the numbers only, then =SUMIF(A2:A10, "Apples", B2:B10) works if the labels are in A and numbers in B. If the data is truly interleaved, consider restructuring or using an array formula like =SUMPRODUCT((A2:A10="Apples")*(A3:A11))—but be careful with offsets and blank cells.

Common Errors and How to Fix Them

  • No matches return 0. If your criteria text doesn’t match any cell exactly, SUMIF returns 0. Check for extra spaces, non-printing characters, or different capitalization. Use =TRIM(A2) to clean data.
  • Criteria with quotes. Text criteria must be enclosed in double quotes, like "Apples". If you reference a cell containing the text, use =SUMIF(A2:A10, D1, B2:B10) where D1 holds the text—no quotes needed around the cell reference.
  • Wildcards not working. Wildcards only work with text criteria, not with cell references unless you concatenate: =SUMIF(A2:A10, "*"&D1&"*", B2:B10).
  • Sum range is different size. If sum_range is not the same size as range, Excel uses the top-left cell and extends it to match. This can cause incorrect results if you accidentally select mismatched ranges.

SUMIF vs. SUMIFS for Text Criteria

SUMIFS is the newer function that handles multiple criteria, and it uses a different argument order: =SUMIFS(sum_range, criteria_range1, criteria1, ...). For a single text criterion, SUMIFS works too: =SUMIFS(B2:B10, A2:A10, "Apples"). The key difference is that SUMIFS always requires the sum_range first, and it supports multiple conditions. If you have only one text condition, either works, but SUMIF is simpler. For multiple text conditions, like summing sales where product is “Apples” and region is “North”, use =SUMIFS(C2:C10, A2:A10, "Apples", B2:B10, "North").

Working with Text That Looks Like Numbers

If your criteria is a number stored as text, Excel may match it to numeric cells as well. For example, =SUMIF(A2:A10, 100, B2:B10) will match cells containing the number 100 or the text “100”. This is usually convenient, but can cause surprises if you have both. To force exact text match, use =SUMIF(A2:A10, "100", B2:B10)—but note that this still matches numeric 100 in many cases. To strictly match text, you may need SUMPRODUCT with ISTEXT: =SUMPRODUCT((A2:A10="100")*(ISTEXT(A2:A10))*B2:B10).

Frequently Asked Questions

Can SUMIF sum text values?

No, SUMIF only sums numeric values. If your sum_range contains text, those cells are ignored. If you need to count text cells, use COUNTIF. If you need to sum numbers that are stored as text, convert them first or use SUMPRODUCT with VALUE.

How do I sum cells that contain a specific word anywhere?

Use wildcards: =SUMIF(A2:A10, "*word*", B2:B10). This sums B values where A contains “word” anywhere. Remember that wildcards work only with text criteria, not with cell references unless you concatenate.

Why does my SUMIF return 0 even though the text matches?

Check for invisible characters like spaces or line breaks. Use =LEN(A2) to see the character count. Also ensure the criteria is in quotes and there are no extra spaces inside the quotes. You can use =TRIM(A2) to clean data.

Can I use SUMIF with a list of text criteria?

SUMIF only accepts one criteria. For multiple text criteria, use SUMIFS with multiple criteria ranges, or use SUMPRODUCT with an array like =SUMPRODUCT(SUMIF(A2:A10, {"Apples","Oranges"}, B2:B10))—this sums both.

Does SUMIF work with partial text matches?

Yes, with wildcards. Use * for any number of characters, ? for a single character. For example, =SUMIF(A2:A10, "A*", B2:B10) sums all values where A starts with “A”.

What if my text has a tilde or wildcard character?

Precede the character with a tilde (~) to treat it as a literal. For example, to match “*” literally, use "~*". To match a tilde itself, use "~~".

Practical Example with Steps

Suppose you have a sales table: A2:A10 contains fruit names (text), B2:B10 contains sales amounts. You want total sales for “Apples”.

  1. Select a cell for the result, say C2.
  2. Type =SUMIF(A2:A10, "Apples", B2:B10).
  3. Press Enter. If A2:A10 has “Apples” in A2, A5, A8 with values 100, 50, 25 in B, the result is 175.

For partial match, use =SUMIF(A2:A10, "App*", B2:B10) to sum all fruits starting with “App”.

Remember: SUMIF is not case-sensitive, so “apples” works too. If you need case-sensitive matching, use SUMPRODUCT with EXACT: =SUMPRODUCT((EXACT(A2:A10,"Apples"))*B2:B10).

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.