Excel SUMIF Exact Text Match: Formula & Fixes

Formulas & functions · Updated November 22, 2026

To make Excel’s SUMIF match an exact text string (not a partial match), use the formula =SUMIF(range, "exact text", sum_range) with the text in double quotes, or reference a cell that contains the exact text. The key is that SUMIF treats text as exact by default, but wildcards like asterisks (*) and question marks (?) can sneak in partial matches, so you must escape them with a tilde (~) when you truly need a literal match.

How SUMIF Handles Text Matching

SUMIF compares each cell in the criteria range to the criteria you supply. For text, the comparison is case-insensitive but otherwise exact: "apple" matches Apple and APPLE, but not apples or pineapple. The function does not do partial matching unless you intentionally include wildcard characters in the criteria. This is different from functions like VLOOKUP with approximate match, or from Excel’s search features that find substrings.

For example, with a list of items in A2:A10 and quantities in B2:B10, the formula =SUMIF(A2:A10, "apple", B2:B10) sums only rows where column A is exactly “apple” (any case). It ignores “green apple”, “apple pie”, and “apples”.

When Exact Match Fails: Wildcards and Hidden Characters

The most common reason a SUMIF returns unexpected results is that your criteria text contains wildcard characters, or your data contains invisible characters like spaces, line breaks, or non-breaking spaces. SUMIF treats * as “any sequence of characters” and ? as “any single character”. So if you want to match the literal text “A*B”, you must write "A~*B" in the criteria. The tilde (~) is the escape character.

Hidden characters are trickier. A cell that looks like “apple” might actually contain “apple ” (with a trailing space) or a line break. To diagnose, use the LEN function to compare the character count of your criteria against the data. For instance, =LEN(A2) returns the number of characters, and if it’s 6 instead of 5, there’s an extra character. You can clean data with the TRIM function or by using Find & Replace (Ctrl+H) to remove non-breaking spaces (copy the character from a cell and paste into the Find box).

Using a Cell Reference for Exact Text

Instead of typing text directly in the formula, you can reference a cell that contains the exact text. This is cleaner when you need to change the criteria frequently. With the criteria in D1, the formula becomes =SUMIF(A2:A10, D1, B2:B10). If D1 contains “apple”, it works exactly as typing the text. But be careful: if D1 contains a wildcard character, SUMIF will still interpret it as a wildcard. To force a literal match even when the cell contains wildcards, you can concatenate the escape tilde: =SUMIF(A2:A10, SUBSTITUTE(D1, "*", "~") & "", B2:B10) — but that only escapes asterisks, not question marks. A more robust approach is to use the SUMPRODUCT function with exact comparison, as shown below.

Alternatives: SUMPRODUCT, SUMIFS, and Exact Match with Wildcards

If you need a strictly literal match that ignores wildcards entirely, use SUMPRODUCT with the equality operator. For example, =SUMPRODUCT((A2:A10="apple")*B2:B10) sums B2:B10 only where A2:A10 equals “apple”, and it treats asterisks and question marks as normal characters. This works in any Excel version. Note that SUMPRODUCT does not handle entire-column references well (like A:A) because it multiplies arrays, so keep the ranges limited.

SUMIFS is the plural version and works the same way for text matching: =SUMIFS(B2:B10, A2:A10, "apple"). It’s useful when you have multiple criteria. Both SUMIF and SUMIFS are case-insensitive. If you need case-sensitive matching, you must use SUMPRODUCT with the EXACT function: =SUMPRODUCT(--EXACT(A2:A10, "apple"), B2:B10).

Common Problems and Troubleshooting

  • Formula returns 0 but you see matching cells: Check for extra spaces, non-printing characters, or that the criteria cell doesn’t have a leading apostrophe (which makes it text). Use TRIM and CLEAN functions.
  • Partial matches are included: Your criteria likely contains a wildcard. For example, "*apple" matches any cell ending with “apple”. Remove wildcards or escape them.
  • Case sensitivity: SUMIF treats “Apple” and “apple” as equal. To differentiate, use SUMPRODUCT with EXACT.
  • Numbers stored as text: If your criteria is a number but the data is text, SUMIF may not match. Convert text to numbers using VALUE or the Text to Columns wizard.
  • Criteria longer than 255 characters: SUMIF has a limit on criteria length. For longer text, use SUMPRODUCT.

Excel Versions and Compatibility

SUMIF and SUMIFS work in all modern versions of Excel, including Excel for Microsoft 365, Excel 2021, 2019, 2016, and earlier. The syntax is identical on Windows, Mac, and Excel for the web. SUMPRODUCT also works everywhere. The only version-specific note is that if you use dynamic array functions like FILTER (available in Excel 2021 and Microsoft 365), you can achieve exact-match sums with =SUM(FILTER(B2:B10, A2:A10="apple")), but that’s not necessary for basic needs.

Practical Example with Step-by-Step

Suppose you have a sales log: Column A has product names (A2:A10), Column B has sales amounts (B2:B10). You want the total sales for the product exactly named “Widget” (not “Widget Pro”).

  1. Click a blank cell, say D2.
  2. Type =SUMIF(A2:A10, "Widget", B2:B10) and press Enter.
  3. The result is the sum of all B values where A equals “Widget”. For example, if B2=100, B5=200, and B7=300 for those rows, the result is 600.

If you later need to change the product, put “Widget” in a cell (say D1) and use =SUMIF(A2:A10, D1, B2:B10).

Frequently Asked Questions

Does SUMIF match text exactly or partially by default? By default, SUMIF matches text exactly, ignoring case, as long as you don’t include wildcard characters. So “apple” matches “apple” but not “apples”.

How do I match text that contains an asterisk or question mark? Use a tilde before the wildcard character in the criteria. For example, to match “A*B”, write "A~*B". This tells Excel to treat the asterisk as a literal character.

Why does my SUMIF return 0 when the text looks identical? The most likely cause is hidden characters like spaces or line breaks. Use =LEN(A2) to check the character count, and clean data with TRIM and CLEAN. Also ensure the criteria cell doesn’t have a leading apostrophe.

Can SUMIF match text case-sensitively? No, SUMIF is case-insensitive. To match case-sensitively, use =SUMPRODUCT(--EXACT(A2:A10, "apple"), B2:B10).

What’s the difference between SUMIF and SUMIFS for exact text? SUMIFS is the plural version and allows multiple criteria. For a single exact text match, both work the same. Use SUMIFS when you need to add more conditions, like date ranges or additional text criteria.

Can I use a cell reference with wildcards and still force exact match? Yes, but you need to escape the wildcards. A robust way is to use SUMPRODUCT with the equality operator, which treats all characters literally. For example, =SUMPRODUCT((A2:A10=D1)*B2:B10) will match exactly what’s in D1, even if it contains an asterisk.

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.