The direct answer: Excel’s SUMIF function cannot directly sum cells that do not contain specific text because its criteria only support equality, comparison operators, and wildcards, not a “does not contain” operator. To sum values where the corresponding text does not contain a given substring, you must use SUMIF with a wildcard trick or switch to SUMPRODUCT with ISNUMBER and SEARCH.
Understanding SUMIF’s Criteria Limitations
SUMIF (and its sibling SUMIFS) evaluates a single condition per range. The criteria argument accepts numbers, text strings, expressions (like ">10"), or wildcards * (any sequence) and ? (single character). There is no built-in operator for “does not contain”—you cannot write something like "<>*text*" because the <> operator only works with exact matches, not wildcard patterns. For example, =SUMIF(A2:A10,"<>*apple*",B2:B10) will sum all cells that are not exactly the string *apple*, which is likely not what you intend. This limitation pushes users toward alternative functions.
The Wildcard Approach with SUMIF
One workaround uses the wildcard for “contains” and subtracts from the total. If you want to sum values in B2:B10 where A2:A10 does not contain the text “apple”, you can sum the entire range and subtract the sum of cells that do contain “apple”:
- Layout: Items in A2:A10, amounts in B2:B10.
- Formula:
=SUM(B2:B10)-SUMIF(A2:A10,"*apple*",B2:B10) - Example: If B2:B10 sums to 100 and the apple-containing rows sum to 30, the result is 70.
This works but has a flaw: if any amount in B is negative, the subtraction logic still holds mathematically, but if your data includes hidden rows or filtered ranges, the total sum may be misleading. Also, if you need to exclude multiple different substrings, this approach becomes cumbersome.
Using SUMPRODUCT for Robust “Does Not Contain”
The most flexible and accurate method is SUMPRODUCT combined with ISNUMBER and SEARCH. SEARCH finds a substring and returns its position (a number) or an error if not found. ISNUMBER converts that to TRUE/FALSE. Then you negate with NOT to get rows that do not contain the text.
- Layout: Items in A2:A10, amounts in B2:B10.
- Formula:
=SUMPRODUCT(--(NOT(ISNUMBER(SEARCH("apple",A2:A10)))),B2:B10) - Explanation:
SEARCHreturns an array of numbers or errors;ISNUMBERmakes TRUE for found;NOTflips to TRUE for not found; the double unary--converts TRUE/FALSE to 1/0;SUMPRODUCTmultiplies each 1/0 by the corresponding amount and sums. - Result: For the same data, if apple rows sum to 30, the formula returns 70.
This method is case-insensitive (SEARCH ignores case). If you need case-sensitive matching, replace SEARCH with FIND.
Comparing SUMIF, SUMIFS, and SUMPRODUCT
Each function serves a different purpose. SUMIF is simple for one condition, SUMIFS handles multiple conditions but still lacks “does not contain”, and SUMPRODUCT is the Swiss Army knife for array calculations. Here’s a quick comparison:
- SUMIF: Fast, easy, but only supports exact match, comparison, or wildcard contains. Does not support “does not contain” directly.
- SUMIFS: Extends SUMIF to multiple criteria, but same wildcard limitation. You can use
"<>*text*"? No—it still fails. - SUMPRODUCT: Handles complex criteria including “does not contain”, works with arrays, and is more flexible. Slightly slower on very large datasets but fine for typical spreadsheets.
For a single exclusion, the subtraction trick with SUMIF is acceptable. For multiple exclusions or dynamic criteria, SUMPRODUCT is the better choice.
Common Mistakes and How to Avoid Them
Users often make these errors:
- Using
"<>*text*"expecting it to exclude substrings—it only excludes exact matches of the literal string with asterisks. - Forgetting that
SEARCHreturns an error when not found, soISNUMBERis essential. - Omitting the double unary
--or multiplying by 1, which causes the formula to return 0 or a #VALUE! error. - Not locking ranges (using absolute references) when copying the formula.
To debug, evaluate the formula with the Evaluate Formula tool (Formulas tab > Formula Auditing > Evaluate Formula) to see the intermediate arrays.
Alternatives: FILTER and LET
If you have Excel 365 or 2021, the FILTER function offers a modern approach. You can filter the amounts based on the text condition and sum the result:
- Layout: Items in A2:A10, amounts in B2:B10.
- Formula:
=SUM(FILTER(B2:B10,NOT(ISNUMBER(SEARCH("apple",A2:A10))))) - This returns the sum of amounts where the item does not contain “apple”.
For more complex scenarios, you can combine with LET to define variables, making the formula easier to read: =LET(items,A2:A10, amounts,B2:B10, SUM(FILTER(amounts, NOT(ISNUMBER(SEARCH("apple",items)))))). These functions require Excel 2021 or Microsoft 365; they are not available in older versions.
Frequently Asked Questions
Can I use SUMIF with “does not contain” using wildcards?
No, SUMIF does not support a wildcard with the not-equal operator. The criteria "<>*text*" only matches cells that are not exactly the string with asterisks. Use the subtraction method or SUMPRODUCT instead.
How do I exclude multiple different texts in a SUMIF?
With SUMPRODUCT, you can combine multiple conditions using * (AND) or + (OR). For example, to exclude both “apple” and “banana”: =SUMPRODUCT(--(NOT(ISNUMBER(SEARCH("apple",A2:A10)))),--(NOT(ISNUMBER(SEARCH("banana",A2:A10)))),B2:B10). This multiplies the two condition arrays, effectively requiring both to be TRUE.
Why does my SUMPRODUCT formula return #VALUE!?
This usually happens when the ranges are not the same size. Ensure A2:A10 and B2:B10 have the same number of rows. Also check for any text values in the sum range—SUMPRODUCT treats text as zero but may error if the arrays are not numeric. Use -- to coerce booleans.
Is there a way to make it case-sensitive?
Yes, replace SEARCH with FIND, which is case-sensitive. For example, =SUMPRODUCT(--(NOT(ISNUMBER(FIND("apple",A2:A10)))),B2:B10) will exclude only lowercase “apple”.
What if I need to sum based on a cell reference for the text?
You can reference a cell containing the text. For SUMPRODUCT, use =SUMPRODUCT(--(NOT(ISNUMBER(SEARCH(D1,A2:A10)))),B2:B10) where D1 holds the substring. For the subtraction method, use =SUM(B2:B10)-SUMIF(A2:A10,"*"&D1&"*",B2:B10).
Performance and Practical Tips
For large datasets (tens of thousands of rows), SUMPRODUCT can slow down because it calculates arrays. In such cases, consider using a helper column with a formula like =NOT(ISNUMBER(SEARCH("apple",A2))) dragged down, then use SUMIF on that helper column with criteria TRUE. This is faster and easier to audit. Also, remember that SEARCH is case-insensitive; if you need case-sensitive, use FIND. Finally, always test your formula on a small sample to verify the logic before applying to the full dataset.
Related
- Excel Rank Values in Range: RANK, RANK.EQ, RANK.AVG
- Excel Print Merge File Showing X? Fixes
- Excel Profit and Loss Template: Free Options
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Concatenate Names in Excel With a Comma?To concatenate names in Excel with a comma and a space, use =TEXTJOIN(", ",TRUE,A2:A4) when the names are…
- Excel Text Join With a New Line: Formulas and TipsTo join text with a new line in Excel, use TEXTJOIN with CHAR(10) as the delimiter. With text…
- XLOOKUP in Excel: What If the Return Cell Is Blank?If XLOOKUP finds a match whose return cell is truly empty, Excel normally displays 0—not a blank. With…
- VLOOKUP to Compare Two Columns on Different SheetsYes. In Excel for Microsoft 365, use an exact-match VLOOKUP to check whether each value in one sheet’s…
- How to Enable Sort and Filter in Excel: Step by StepTo enable sorting and filtering in Excel for Microsoft 365 on Windows, click a cell in your data…
- Excel Formula to Remove Duplicates From a List AutomaticallyWith values in A2:A20, enter =UNIQUE(A2:A20) in an empty cell to return one copy of each distinct value.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.