To sum values that do not match a criterion, use the not-equal operator <> in SUMIFS. For example, with categories in A2:A7 and amounts in B2:B7, =SUMIFS(B2:B7,A2:A7,"<>North") adds amounts for every category other than North.
How SUMIFS handles not-equal criteria
SUMIFS adds values in a sum range only when all of its criteria are satisfied. Its syntax is SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...). The not-equal operator is written as two angle brackets, <>, and goes inside the criterion text. It is not a separate argument or a comparison typed outside the formula.
In the example above, the formula checks each cell in A2:A7 against the text North. It then adds the corresponding value from B2:B7 when the category is anything other than North. If the rows contain North/120, South/80, North/50, East/40, North/0, and South/70, the result is 190: 80 + 40 + 70. The three North rows are excluded.
The ranges must cover corresponding rows and have the same dimensions. If the sum range is B2:B100, the criteria range should normally be A2:A100—not A2:A99 or an entire column paired with only a few cells. Misaligned ranges can cause an error or make the formula refer to the wrong records.
Use a cell reference for the value to exclude
A hard-coded criterion is convenient for a quick calculation, but a cell reference is easier to maintain when the excluded value may change. With categories in A2:A7, amounts in B2:B7, and the value to exclude in D2, use:
With categories in A2:A7, amounts in B2:B7, and the excluded category in D2: =SUMIFS(B2:B7,A2:A7,"<>"&D2) returns 190 when D2 contains North.
The ampersand joins the operator and the cell value to make one criterion. If D2 changes to South, the formula recalculates and excludes South instead. This is useful for reports with a selector cell, a changing product name, or a value entered by a user. Leave the criterion cell’s contents as the value itself; do not type the not-equal operator into D2 as well, or the assembled criterion will not mean what you intend.
Exclude blanks, or exclude a value and blanks
To sum rows where a criteria cell is not blank, use "<>" as the criterion. With categories in A2:A100 and amounts in B2:B100, =SUMIFS(B2:B100,A2:A100,"<>") sums amounts for rows whose category cells are nonblank. This is different from excluding a particular category: it includes every nonblank category, whatever its text or number.
If you want to exclude both a named value and blank cells, add a second criterion pair using the same criteria range. For example, with categories in A2:A100 and amounts in B2:B100, =SUMIFS(B2:B100,A2:A100,"<>North",A2:A100,"<>") excludes North and blank category cells. SUMIFS applies separate criteria pairs as AND conditions, so a row must meet both tests to contribute to the total.
Be aware that a cell that looks blank may contain a formula returning an empty string, spaces, or another invisible character. Those are not always treated like a truly empty cell by formulas and may behave differently from what you expect. Check a suspicious cell in the formula bar, or test the data in a helper column, before concluding that SUMIFS has ignored the criterion.
Exclude more than one value
Criteria pairs in SUMIFS are combined with AND, not OR. To exclude North and South from the same category range, add both not-equal conditions. With categories in A2:A7 and amounts in B2:B7, =SUMIFS(B2:B7,A2:A7,"<>North",A2:A7,"<>South") returns 40 for the example data above, because only the East row remains.
Another approach is to subtract the totals for excluded categories from a total that includes all categories. However, that can be harder to audit, and it needs careful handling if the original total has its own filters or blank rules. For a short exclusion list, repeated criteria pairs are usually clear. For a long, changing list of exclusions, a helper column or a more structured formula may be easier to maintain than a very long SUMIFS expression.
Do not try to list alternative exclusions in one ordinary criterion by separating them with commas. A single criterion such as North,South is treated as one text value, not as two separate values to exclude. If your goal is to include either North or South, that is an OR condition and requires a different setup, such as adding separate SUMIFS results or using a helper column.
Use not equal with dates, numbers, and wildcards
Not-equal criteria can compare numbers as well as text. For instance, a criterion of "<>0" excludes zero-valued cells in the criteria range. Whether that is the right test depends on the data: a true zero, an empty cell, and a number stored as text may not be equivalent for your purpose. If the values are held in another cell, join the operator to that reference rather than embedding a fixed value.
Dates in Excel are stored as serial numbers, so a date criterion should be built from a real date value rather than an ambiguous text string. With dates in A2:A100 and amounts in C2:C100, =SUMIFS(C2:C100,A2:A100,"<>"&DATE(2026,1,1)) excludes rows dated January 1, 2026. This assumes the cells in A contain actual Excel dates; text that merely looks like a date may not compare as expected. If blank dates should also be excluded, add another criteria pair for A2:A100 with the criterion "<>".
Criteria can also use wildcards for text matching. An asterisk matches any sequence of characters, and a question mark matches one character. For example, a criterion assembled from a cell containing *Ltd can exclude text values ending in Ltd, rather than just one exact company name. To match an asterisk or question mark as a literal character, put a tilde before it in the criterion. Wildcard criteria are useful for patterns, but check whether the pattern excludes more rows than intended.
Why an Excel SUMIFS not-equal formula can give the wrong result
When the result seems too high, too low, or unexpectedly zero, check the data and the ranges before rewriting the formula. Common causes include:
- Extra spaces or inconsistent text: North and North followed by a space are different values. A criterion excluding North may not exclude the spaced version. Use Find and Replace carefully, or clean the source data with a helper column.
- Numbers or dates stored as text: A cell can look numeric or date-like while being text. Convert or normalize the source values so the criteria and data use compatible types.
- Unexpected blanks: Empty cells, formulas returning empty strings, and cells containing spaces can behave differently. Inspect the underlying cell contents instead of relying only on appearance.
- Wrong range boundaries: Confirm that the sum range and criteria range start and end on the same records, and that the formula includes newly added rows.
- Wrong logical expectation: Multiple criteria pairs are AND conditions. Every condition must be true; SUMIFS does not interpret them as alternatives.
- Case assumptions: SUMIFS text criteria are not case-sensitive. It cannot use a standard not-equal text criterion to exclude only one capitalization while retaining another.
For a quick check, filter the source table to the records that should qualify and compare their visible amounts with the formula result. You can also put the suspected criterion in a cell and inspect it for spaces. If the data is in an Excel Table, structured references can make formulas expand as records are added, but the same not-equal criterion rules still apply.
SUMIFS versus SUMIF and other approaches
SUMIF handles one condition, while SUMIFS can handle several conditions and is written with the sum range first. For a single not-equal test, SUMIFS is still a straightforward choice; adding future conditions is easier because the function already supports multiple criteria pairs. Be careful when switching functions: SUMIF uses a different argument order, so copying a SUMIFS formula and changing only the function name can produce an incorrect result.
If the criteria are complex, a helper column can make the rule visible row by row. For example, a helper column can label records as Include or Exclude, and SUMIFS can then sum only Include rows. This can be easier for colleagues to review than a nested formula, especially when the exclusion rule changes often. For a one-off total or a short set of simple conditions, a direct SUMIFS formula is generally more compact.
Frequently Asked Questions
What is the not-equal symbol in Excel SUMIFS?
Use <> inside a quoted criterion, such as "<>North". To compare against a cell, join the operator and reference with an ampersand, as in "<>"&D2.
Does SUMIFS with not equal include blank cells?
A criterion excluding one specific value can allow blank criteria cells to qualify, so add a separate "<>" condition if blanks must also be excluded. Verify cells that look blank but may contain spaces or formulas returning empty text.
Can SUMIFS exclude two values at once?
Yes. Add two criteria pairs for the same criteria range, one for each value to exclude. The pairs are AND conditions, so a row must differ from both values to be included.
Is SUMIFS not equal case-sensitive?
No. Standard SUMIFS text comparisons are case-insensitive, so a criterion excluding North also excludes NORTH and north. If capitalization itself determines which rows qualify, use a different approach designed for case-sensitive comparison.
Why does SUMIFS return zero when I use not equal?
Check for mismatched ranges, text stored as numbers or dates, unexpected spaces, and criteria that do not match the source data. Also confirm that qualifying rows contain numeric values in the sum range; text amounts are not added as numeric amounts.
Related
- Excel Data Model Measures: Add, Format, and Use One
- Excel Data Model: How to Append Tables with Power Query
- Excel Data Run Analysis: A Practical Runs Test
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel REDUCE Function Examples With NumbersIf your numbers are in A2:A6, add them with =REDUCE(0,A2:A6,LAMBDA(acc,n,acc+n)). With 12, -3, 8, 0, and 5 in…
- How to Add Page Numbers in Word Bottom Right CornerTo add page numbers at the bottom right in Word for Microsoft 365 on Windows, select Insert >…
- Excel Formula for Percentage of a NumberTo calculate a percentage of a number in Excel, multiply the number by the percentage: if A2 contains…
- Excel INDEX Absolute References: When and How to Use ThemYes, an Excel INDEX formula can use absolute references, and locking its ranges is often helpful when you…
- Excel Text Unique Values: Extract, Count, and CompareYes. In Excel for Microsoft 365, use UNIQUE to return a list of distinct text values from a…
- Excel Date Subtraction: Formulas, Days, and WorkdaysTo subtract one date from another in Excel, subtract the earlier date cell from the later one: if…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.