Excel SUMIF Equals Text: Formula & Examples

Formulas & functions · Updated November 22, 2026

Yes, you can use SUMIF with text criteria in Excel. The formula is =SUMIF(range, "text", sum_range) where the second argument is the text you want to match exactly. For example, =SUMIF(A2:A10, "Apples", B2:B10) sums all values in B2:B10 where the corresponding cell in A2:A10 equals “Apples”. This works in Excel for Microsoft 365, Excel 2021, 2019, and earlier versions.

How SUMIF Handles Text Criteria

SUMIF compares text values exactly, but it is not case-sensitive. So “apples” and “APPLES” are treated the same. It matches whole cells, meaning if a cell contains “Apple Pie”, it will not match “Apples”. To match partial text, you use wildcards: * for any sequence of characters, ? for any single character, and ~ to escape a wildcard if you need to match a literal asterisk or question mark.

Exact Match vs. Wildcard Match

For an exact match, just put the text in quotes: =SUMIF(A2:A10, "Apples", B2:B10). For a partial match, add asterisks: =SUMIF(A2:A10, "*Apple*", B2:B10) sums all cells containing the word “Apple” anywhere. To match text that starts with “Apple”, use "Apple*". To match text that ends with “Apples”, use "*Apples".

If you need a case-sensitive match, SUMIF cannot do it. Use the SUMPRODUCT function with EXACT: =SUMPRODUCT((EXACT(A2:A10,"Apples"))*B2:B10). This returns the sum only for cells that exactly equal “Apples” with that capitalization.

Referencing a Cell for the Text Criterion

Instead of typing text directly, you can reference a cell. For example, with the text in D1, use =SUMIF(A2:A10, D1, B2:B10). If you need to use wildcards with a cell reference, concatenate the asterisks: =SUMIF(A2:A10, "*"&D1&"*", B2:B10). This is useful when you want to change the criteria without editing the formula.

Common Problems and Solutions

Problem: The formula returns 0 even though there are matches. Check for extra spaces in the data. A cell with “Apples ” (trailing space) will not match “Apples”. Use the TRIM function to clean data, or use a wildcard like "*Apples*" to ignore spaces. Also check that the sum range is the correct size and shape. If the criteria range is A2:A10, the sum range must be B2:B10 (same number of rows).

Problem: Text is stored as a number. If a cell looks like text but is actually a number, SUMIF may not match. Use the TEXT function to convert, or ensure the data is entered as text.

Problem: Need to sum based on multiple text criteria. SUMIF only handles one criterion. Use SUMIFS with multiple criteria ranges. For example, to sum sales for “Apples” in the “North” region: =SUMIFS(C2:C10, A2:A10, "Apples", B2:B10, "North").

Alternatives: SUMIFS, SUMPRODUCT, and FILTER

SUMIFS is the modern replacement for SUMIF when you have multiple criteria. It works with text the same way, but the syntax is different: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). For a single text criterion, SUMIFS is also fine: =SUMIFS(B2:B10, A2:A10, "Apples").

SUMPRODUCT offers more flexibility, especially for case-sensitive matches or when you need to use functions like LEFT or RIGHT. For example, to sum sales for items starting with “A”: =SUMPRODUCT((LEFT(A2:A10,1)="A")*B2:B10).

If you have Excel 365, you can use FILTER with SUM: =SUM(FILTER(B2:B10, A2:A10="Apples")). This is more readable and can be extended easily.

Comparing SUMIF with VLOOKUP for Text

SUMIF sums values, while VLOOKUP retrieves a single value. They serve different purposes. If you need to sum all matching values, SUMIF is correct. If you need to find one specific value based on a text lookup, use VLOOKUP or XLOOKUP. For example, =VLOOKUP("Apples", A2:B10, 2, FALSE) returns the first matching value, not a sum.

Frequently Asked Questions

Q: Can SUMIF match text that contains a question mark or asterisk literally? Yes, use a tilde (~) before the wildcard. For example, =SUMIF(A2:A10, "*~?*", B2:B10) sums cells that contain a literal question mark.

Q: Does SUMIF work with text that has leading or trailing spaces? No, it does an exact match, so spaces matter. Use TRIM on your data or use wildcards to ignore spaces.

Q: Can I use SUMIF with text in another sheet? Yes, reference the sheet name: =SUMIF(Sheet2!A2:A10, "Apples", Sheet2!B2:B10).

Q: What if my text criteria is longer than 255 characters? SUMIF may fail. Use SUMPRODUCT with EXACT, or split the text into smaller parts.

Q: Is SUMIF case-sensitive? No, it is not. For case-sensitive matching, use SUMPRODUCT with EXACT.

Q: How do I sum text values that are numbers stored as text? SUMIF will match them if the criteria is also text. For example, =SUMIF(A2:A10, "123", B2:B10) matches cells that contain the text “123”.

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.