Excel SUMIF Not Equal to Text: Formula & Examples

Formulas & functions · Updated November 22, 2026

Yes, you can use SUMIF to sum cells where a corresponding text value is not equal to a specific text. The formula is =SUMIF(range, "<>text", sum_range), where "<>text" is the criteria that excludes that exact text. For example, =SUMIF(A2:A10, "<>Pending", B2:B10) sums all amounts in B2:B10 where the status in A2:A10 is not the word “Pending”.

How SUMIF Handles Text Criteria

SUMIF’s criteria argument accepts text, numbers, expressions, or wildcards. When you want to exclude a specific text value, you use the not-equal operator <> combined with the text in quotes. The syntax is =SUMIF(range, "<>text", [sum_range]). If you omit sum_range, Excel sums the cells in range itself. The operator must be inside the quotes along with the text, like "<>Completed". This works in all modern versions of Excel, including Excel for Microsoft 365, Excel 2021, Excel 2019, and earlier versions. On Mac, the formula works identically; there is no difference in syntax.

Using Wildcards with Not-Equal Text

Sometimes you need to exclude text that matches a pattern rather than an exact string. SUMIF supports wildcards: * (any sequence of characters) and ? (any single character). For instance, to sum amounts where the product code in column A does not start with “X”, use =SUMIF(A2:A20, "<>X*", B2:B20). This excludes any text that begins with the letter X. To exclude text that contains a specific substring, use "<>*substring*". For example, =SUMIF(A2:A20, "<>*temp*", B2:B20) sums amounts where the text in A does not contain “temp”. Note that wildcards work only with text criteria; they do not apply to numeric or date criteria.

Common Errors and How to Fix Them

One frequent mistake is forgetting the quotes around the criteria. If you write =SUMIF(A2:A10, <>Pending, B2:B10), Excel returns a #NAME? error because it treats Pending as a named range. Always enclose the entire criteria in double quotes, including the operator and text. Another issue is mixing data types: SUMIF treats numbers and text differently. If your range contains numbers stored as text (e.g., a cell shows “123” but is left-aligned), the not-equal text criteria may not match as expected. To avoid this, ensure your data is consistently formatted. Also, remember that SUMIF is case-insensitive: "<>pending" will exclude “Pending”, “PENDING”, and “pending”. If you need case-sensitive exclusion, you must use SUMPRODUCT with EXACT, as SUMIF cannot do case-sensitive matching.

Alternatives: SUMIFS, SUMPRODUCT, and FILTER

SUMIFS is the modern replacement that supports multiple criteria. To sum with a not-equal condition, use =SUMIFS(sum_range, criteria_range, "<>text"). For example, =SUMIFS(B2:B20, A2:A20, "<>Closed"). SUMIFS is more flexible and recommended for new workbooks. SUMPRODUCT offers even more power, especially for case-sensitive or array-based logic. For case-sensitive exclusion, use =SUMPRODUCT((NOT(EXACT(A2:A20, "Pending")))*(B2:B20)). In Excel 365, you can also use the FILTER function: =SUM(FILTER(B2:B20, A2:A20<>"Pending")). FILTER is dynamic and spills results, but it requires Excel 365 or Excel 2021. Choose SUMIF for simple single-condition sums, SUMIFS for multiple conditions, SUMPRODUCT for case-sensitive or complex logic, and FILTER when you want a dynamic array approach.

Practical Examples with Step-by-Step Layout

Assume you have a sales log: Column A lists region (text), Column B lists amount (numbers). Data in rows 2 to 10. To sum amounts for all regions except “East”:

  • In cell D2, type: =SUMIF(A2:A10, "<>East", B2:B10)
  • Press Enter. The result is the sum of all B values where A is not “East”.

If your data has a header row and you want to exclude multiple text values, you cannot do it directly with one SUMIF. Use SUMIFS with multiple criteria ranges, or use SUMPRODUCT with array logic. For example, to sum amounts where region is not “East” and not “West”, use =SUMPRODUCT((A2:A10<>"East")*(A2:A10<>"West")*B2:B10). This multiplies the boolean arrays (TRUE/FALSE) and then sums the products. The formula returns the correct total. Always test with a small dataset to verify the arithmetic.

Performance and Best Practices

SUMIF is a fast, efficient function that handles large datasets well. However, when using wildcards or multiple criteria, consider using SUMIFS or SUMPRODUCT for clarity. For very large datasets (hundreds of thousands of rows), SUMIFS is generally faster than SUMPRODUCT because it uses optimized internal logic. Avoid using entire column references like A:A unless necessary, as they can slow down calculation. Instead, use a defined range like A2:A10000. Also, be mindful of hidden rows: SUMIF does not ignore hidden rows; it sums all cells in the range regardless of filtering. If you need to sum only visible rows, use SUBTOTAL or AGGREGATE with function 9 (SUM) and a filter, or use a helper column with visibility flags. For Excel tables, you can use structured references like =SUMIF(Table1[Region], "<>East", Table1[Amount]), which makes formulas easier to read and maintain.

Frequently Asked Questions

Can I use SUMIF to sum cells that are not equal to a blank cell? Yes. Use =SUMIF(A2:A10, "<>", B2:B10) to sum amounts where column A is not blank. Note that this excludes truly empty cells but includes cells with zero-length text (e.g., a formula that returns “”). To exclude those as well, use =SUMIF(A2:A10, "<>"&"", B2:B10) or better, use =SUMIFS(B2:B10, A2:A10, "<>", A2:A10, "<>"&"").

Why does my SUMIF with “<>text” return 0 even though there are matching cells? This often happens when the text in your criteria range has extra spaces or is a different case. SUMIF is case-insensitive, so case isn’t the issue. Check for leading/trailing spaces using the TRIM function. Also, ensure the criteria text is spelled exactly as it appears, including any punctuation. If the cells contain numbers stored as text, the not-equal text criteria may not match. Try using =SUMIF(A2:A10, "<>"&C1, B2:B10) where C1 contains the text you want to exclude, to avoid typing errors.

Does SUMIF work with dates when using not-equal? Yes, but dates are stored as numbers in Excel. To exclude a specific date, use the DATE function or a cell reference: =SUMIF(A2:A10, "<>"&DATE(2025,1,1), B2:B10). If you want to exclude all dates in a certain year, use wildcards with text? No, that won’t work because dates are numeric. Instead, use SUMIFS with multiple conditions like =SUMIFS(B2:B10, A2:A10, "<>", A2:A10, ">="&DATE(2025,1,1), A2:A10, "<="&DATE(2025,12,31)) to exclude a range.

Can I use SUMIF to sum when a cell does not equal one of several text values? Not directly. SUMIF only accepts a single condition. Use SUMIFS with multiple criteria ranges, or use SUMPRODUCT with array logic as shown earlier. For example, =SUMPRODUCT((A2:A10<>"Red")*(A2:A10<>"Blue")*B2:B10) sums amounts where the color is neither Red nor Blue.

Is there a way to make SUMIF case-sensitive for not-equal text? No, SUMIF and SUMIFS are case-insensitive. For case-sensitive exclusion, use SUMPRODUCT with EXACT: =SUMPRODUCT(--(NOT(EXACT(A2:A10, "Pending"))), B2:B10). The EXACT function compares text exactly, including case, and returns TRUE only for exact matches. The double unary (--), also called double negative, converts TRUE/FALSE to 1/0 so SUMPRODUCT can multiply and sum.

Comparison: SUMIF vs SUMIFS vs SUMPRODUCT

SUMIF is the simplest and works for a single condition. SUMIFS is the modern standard, supports multiple conditions, and is easier to read when you have several criteria. SUMPRODUCT is the most flexible, allowing array operations, case-sensitive matching, and complex logic, but it can be slower on very large datasets. For a simple not-equal text condition, SUMIF is perfectly fine. If you are building a complex model with many criteria, SUMIFS is recommended. If you need case sensitivity or want to combine multiple not-equal conditions without adding helper columns, SUMPRODUCT is your best bet. In Excel 365, FILTER is a dynamic alternative that can be more intuitive for some users, but it requires a newer version. Choose the function that balances clarity, performance, and your specific needs.

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.