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”).
- Click a blank cell, say D2.
- Type
=SUMIF(A2:A10, "Widget", B2:B10)and press Enter. - 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
- Excel Mobile Repair Grand Rapids: Fast Phone Fixes
- Excel Month of Date as Text: 5 Easy Ways
- Free Excel Mileage Log Templates: 7 Real Options
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel F4: How to Lock Cell References in FormulasTo lock a cell reference in an Excel formula, edit the formula, place the cursor in or select…
- How to Compare Two Columns in Excel: Formulas and TipsTo compare values on the same row, put the lists in columns A and B, then enter a…
- Microsoft Excel Filter Duplicates: Find or Remove ThemExcel does not have a dedicated “filter duplicates” command in a column’s filter menu. To show repeated records,…
- Excel Filter by Text Length: Steps and FormulasTo filter Excel rows by the number of characters in a text cell, add a helper column with…
- Excel Formula to Highlight Every Other Row AutomaticallyTo highlight every other row in Excel, select the range, open Home > Conditional Formatting > New Rule,…
- Structured References in Excel Formulas: A Practical GuideStructured references are Excel table references that use a table’s name and column headings instead of ordinary cell…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.