To sum values in Excel where a corresponding cell does not contain specific text, use the SUMIF function with a wildcard and the not-equal operator: =SUMIF(range,"<>*text*",sum_range). For example, =SUMIF(A2:A10,"<>*pending*",B2:B10) sums all amounts in B2:B10 where the text in A2:A10 does not contain the word “pending” anywhere.
Why SUMIF Needs a Wildcard for “Does Not Contain”
Excel’s SUMIF criteria are text-based. When you type "<>pending", Excel interprets it as “not exactly equal to the word pending.” That works only for exact matches. To check whether a cell contains a substring, you must use the asterisk wildcard *, which represents any number of characters. So "<>*pending*" means “not equal to anything that has ‘pending’ anywhere in it.” Without the asterisks, a cell like “Pending invoice” or “not pending” would be included incorrectly.
Basic Formula with a Worked Example
Assume you have a list of project statuses in A2:A10 and amounts in B2:B10. You want the total of all amounts where the status is not “complete.”
- In A2:A10: “complete”, “in progress”, “complete”, “on hold”, “complete”, “in progress”, “complete”, “complete”, “on hold”
- In B2:B10: 100, 200, 150, 300, 250, 400, 350, 500, 450
Formula: =SUMIF(A2:A10,"<>*complete*",B2:B10)
This sums B2, B4, B6, B9, and B10 (the non-complete rows): 200 + 300 + 400 + 500 + 450 = 1850.
If you instead used "<>complete", it would exclude only exact “complete” cells, but would still include “in progress” and “on hold” – same result here because no cell has extra characters. To see the difference, change one cell to “complete (final)”. The wildcard version excludes it; the exact version includes it.
How to Choose Between SUMIF, SUMIFS, and SUMPRODUCT
For a single “does not contain” condition, SUMIF is simplest. But if you need multiple conditions (e.g., not containing “pending” AND also not containing “cancelled”), use SUMIFS with multiple criteria ranges. However, SUMIFS does not support wildcards in the same way for “not contains” – you can use "<>*pending*" as a criteria, but combining two “not contains” conditions with AND is tricky because SUMIFS uses AND logic across criteria, so a row would need to satisfy both. For OR logic (not containing either), use SUMPRODUCT with ISNUMBER(SEARCH()).
Example with SUMPRODUCT to sum amounts where status does not contain “pending” or “cancelled”:
=SUMPRODUCT(B2:B10 * (ISNUMBER(SEARCH("pending",A2:A10))=FALSE) * (ISNUMBER(SEARCH("cancelled",A2:A10))=FALSE))
This works in all Excel versions. Note that SEARCH is case-insensitive; use FIND for case-sensitive.
Case Sensitivity and Partial Matches
SUMIF is not case-sensitive by default. "<>*pending*" will exclude cells with “Pending”, “PENDING”, or “pending”. If you need case-sensitive matching, use SUMPRODUCT with ISNUMBER(FIND("pending",A2:A10)) because FIND is case-sensitive. Also note that wildcards match partial text anywhere in the cell. To match only whole words (e.g., exclude “cat” but not “category”), you cannot use a simple wildcard; you would need a more complex formula, such as checking for spaces or using SUMPRODUCT with TRIM and SUBSTITUTE.
Common Pitfalls and Errors
- Using
"<>text"without asterisks – only excludes exact matches, not contains. - Forgetting the sum_range argument – if omitted, SUMIF sums the range itself, which may give unexpected results.
- Criteria with leading/trailing spaces – if your data has extra spaces, “pending ” won’t match “pending”. Use
TRIMor clean data. - Using
"<>*"to sum non-blank cells – this actually sums all non-empty cells, but if you want to exclude cells that contain any text, use"<>"&""or"?*". - Wildcard characters in the text itself – if the text you’re excluding contains an asterisk or question mark, you need to escape it with a tilde (
~), e.g.,"<>*~**"to exclude cells containing an asterisk.
Alternatives for More Complex “Not Contains” Logic
If you need to sum based on “does not contain” across multiple columns or with OR logic, consider these alternatives:
- SUMPRODUCT – as shown above, flexible for any condition, but can be slower on large datasets.
- FILTER function (Excel 365/2021) –
=SUM(FILTER(B2:B10,ISNUMBER(SEARCH("pending",A2:A10))=FALSE)). This is easier to read and can be extended. - Helper column – add a column with a formula like
=IF(ISNUMBER(SEARCH("pending",A2)),"exclude","include"), then useSUMIFon that helper.
Each has trade-offs. SUMIF is fastest and simplest for a single condition. SUMPRODUCT is more flexible but can be slower with thousands of rows. FILTER is modern and readable but requires a recent Excel version.
Frequently Asked Questions
How do I sum cells that do not contain a specific word but may contain other text?
Use =SUMIF(range,"<>*word*",sum_range). The asterisks on both sides match any characters before and after the word, so “not containing” means the word does not appear anywhere in the cell.
Can I use SUMIF to exclude cells that contain any of several words?
Not directly with a single SUMIF. Use SUMPRODUCT with multiple ISNUMBER(SEARCH()) conditions, or use SUMIFS with multiple criteria ranges, but remember SUMIFS uses AND logic. For OR logic, SUMPRODUCT is the way.
Why does my SUMIF with “<>*text*” still include cells that contain the text?
Check that the criteria is typed exactly as "<>*text*" – note the asterisks are inside the quotes. If you used "<>text" or "<>*text" (only one asterisk), it won’t work as expected. Also ensure there are no extra spaces in the criteria or the data.
Is there a way to make SUMIF case-sensitive for “does not contain”?
No, SUMIF is always case-insensitive. Use SUMPRODUCT with FIND (case-sensitive) or a helper column with FIND and then SUMIF.
Does SUMIF work with numbers that are stored as text?
Yes, but be careful. If your criteria range contains numbers stored as text, the wildcard matching still works. The sum_range can contain numbers or text; SUMIF will sum numeric values only. If the sum_range contains text numbers, they are ignored.
Can I use a cell reference for the text in the criteria?
Yes, concatenate the wildcards with the cell reference: =SUMIF(A2:A10,"<>*"&D1&"*",B2:B10) where D1 contains the word to exclude. This is useful when the excluded text changes.
Related
- Excel Power Query Merge 3 Queries: Step-by-Step
- Excel Power Query Incremental Refresh: How to Set It Up
- Excel Power Query: Append to Existing Table
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Minus Amounts: Examples and TipsTo subtract one amount from another in Excel, enter a formula with the minus sign: if the first…
- Excel Date Minus 1 Month? Formula and Date RulesTo subtract one calendar month from an Excel date, use =EDATE(A1,-1), where A1 contains a real Excel date.…
- String Functions in Java: GeeksforGeeks GuideString functions in Java, as covered on GeeksforGeeks, are built-in methods of the String class that let you…
- TRIM in Excel for Text Cleanup: What It RemovesIn Excel for Microsoft 365 on Windows, use TRIM to remove ordinary spaces at the beginning and end…
- COUNTIF Function to Find Duplicates in Excel: A GuideYes. In Excel, COUNTIF can find duplicates by counting how often each value appears: with values in A2:A7,…
- Excel Function to Check If a Cell Contains TextTo check whether a cell contains a particular word or phrase, use =ISNUMBER(SEARCH("text",A2)), replacing “text” with the text…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.