Excel SUMIF Text Values: How to Sum Cells That Contain Text

Formulas & functions · Updated November 22, 2026

The direct answer: Excel’s SUMIF function does not sum text values themselves—it sums numbers in a range based on a text condition you specify. For example, =SUMIF(A2:A10,"Apple",B2:B10) adds all numbers in B2:B10 where the corresponding cell in A2:A10 equals “Apple”. If you need to sum cells that contain text as numbers (like “100” stored as text), use SUMPRODUCT or convert the text to numbers first.

What SUMIF Actually Does

SUMIF is a conditional summing function. Its syntax is SUMIF(range, criteria, [sum_range]). The range is where Excel looks for the condition, the criteria is what to match (text, number, or expression), and the optional sum_range is the actual cells to add. If you omit sum_range, Excel sums the cells in range itself.

Key point: SUMIF only adds numeric values. Text entries in the sum range are ignored. So if your data has “100” (text) in B2, SUMIF will not include it unless you convert it to a real number.

Using Text Criteria in SUMIF

You can use text criteria in several ways. Here are common examples with the layout: names in A2:A20, sales amounts in B2:B20.

  • Exact match: =SUMIF(A2:A20,"Apple",B2:B20) sums sales for rows where A equals “Apple” (case-insensitive).
  • Cell reference: =SUMIF(A2:A20,D1,B2:B20) where D1 contains the text you want to match.
  • Wildcard for partial match: =SUMIF(A2:A20,"*Apple*",B2:B20) sums rows where A contains “Apple” anywhere.
  • Wildcard for starting with: =SUMIF(A2:A20,"App*",B2:B20) sums rows where A starts with “App”.
  • Not equal to: =SUMIF(A2:A20,"<>Apple",B2:B20) sums rows where A is not “Apple”.

Note: wildcards (* and ?) work with text criteria but not with numeric criteria. To match a literal asterisk, use a tilde (~*).

Summing Text That Looks Like Numbers

Sometimes cells contain numbers stored as text—often imported from other systems. If you need to sum those, SUMIF won’t work directly because the sum range contains text. Instead, use SUMPRODUCT with VALUE to convert and sum in one step.

Example: A2:A10 has product names, B2:B10 has amounts stored as text (e.g., “100”). To sum amounts for “Apple”:

=SUMPRODUCT((A2:A10="Apple")*VALUE(B2:B10))

This returns the sum of the converted numbers. If any cell in B2:B10 is not a valid number, VALUE returns an error. To avoid that, you can use IFERROR inside an array formula, but that requires entering with Ctrl+Shift+Enter in older Excel versions. In Microsoft 365, you can use:

=SUM(IF(A2:A10="Apple",VALUE(B2:B10),0))

Entered normally in Excel 365 (dynamic arrays). In older versions, press Ctrl+Shift+Enter.

Common Problems and Solutions

Here are frequent issues users encounter with SUMIF and text:

  • No sum returned (0): Check for extra spaces in the criteria or data. Use =SUMIF(A2:A20,"Apple",B2:B20) but if A2 has “Apple ” (trailing space), it won’t match. Use TRIM in a helper column or use wildcards: "*Apple*".
  • Case sensitivity: SUMIF is not case-sensitive. If you need case-sensitive matching, use SUMPRODUCT with EXACT: =SUMPRODUCT((EXACT(A2:A20,"Apple"))*B2:B20).
  • Criteria cell contains wildcard characters: If D1 contains “*”, SUMIF treats it as a wildcard. To match a literal asterisk, use =SUMIF(A2:A20,"~*",B2:B20) or SUBSTITUTE.
  • Sum range is a different size: SUMIF requires range and sum_range to be the same size. If they aren’t, Excel may return incorrect results or an error.

SUMIF vs. SUMIFS for Multiple Text Conditions

When you need to sum based on multiple text criteria, use SUMIFS. Its syntax is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). Example: sum sales for “Apple” in region “West”:

=SUMIFS(B2:B20, A2:A20, "Apple", C2:C20, "West")

Note the order: sum_range comes first in SUMIFS, unlike SUMIF where it’s last. SUMIFS also supports wildcards and cell references.

Alternatives for Summing Text Values

If you need to sum text entries themselves (like counting how many times “Apple” appears), use COUNTIF instead. For summing numbers stored as text, consider these alternatives:

  • Convert text to numbers: Select the range, click the warning icon, and choose “Convert to Number”. Or use Paste Special > Multiply by 1.
  • Use SUMPRODUCT with VALUE as shown above.
  • Use SUM with array formula in Excel 365: =SUM(VALUE(B2:B10)) entered normally in 365 (dynamic array).
  • Use Power Query to change data types before loading.

Frequently Asked Questions

Can SUMIF sum text values like “Apple”?

No, SUMIF sums numbers only. If you want to count how many times “Apple” appears, use COUNTIF. If you have numeric values stored as text, you need to convert them or use SUMPRODUCT with VALUE.

How do I sum cells that contain specific text in Excel?

Use SUMIF with a wildcard. For example, to sum sales for rows where product name contains “Apple”, use =SUMIF(A2:A20,"*Apple*",B2:B20). This matches any cell with “Apple” anywhere.

Why is my SUMIF returning 0 when the text matches?

Common reasons: extra spaces in the data or criteria, the criteria cell contains a formula that returns text with hidden characters, or the sum range contains text numbers. Use TRIM to clean data, or check with LEN.

How do I sum if a cell contains text and another condition?

Use SUMIFS with multiple criteria. For example, =SUMIFS(B2:B20,A2:A20,"*Apple*",C2:C20,"West") sums sales where product contains “Apple” and region is “West”.

Is SUMIF case-sensitive?

No, SUMIF is not case-sensitive. For case-sensitive matching, use SUMPRODUCT with EXACT: =SUMPRODUCT((EXACT(A2:A20,"Apple"))*B2:B20).

Can I use SUMIF with text criteria that includes a question mark?

Yes, but the question mark is a wildcard. To match a literal question mark, use a tilde before it: "~?". For example, =SUMIF(A2:A20,"~?",B2:B20) sums rows where A contains a single question mark.

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.