Excel SUMIF Does Not Contain Text: Formula & Examples

Formulas & functions · Updated November 22, 2026

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 TRIM or 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 use SUMIF on 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

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.