SUMIFS Not Equal To: Exclude Values From a Total

To use SUMIFS with not equal to, write the criteria as "<>value": =SUMIFS(D2:D8,C2:C8,"<>Cancelled") adds every order except the cancelled ones. You can exclude several values by repeating the range, mix "not equal" with normal conditions, and point the criteria at a cell. This guide shows each case on one order table, plus the mistake that makes totals too big.

All the examples use this table in A1:D8. The amounts add up to 5,400 in total.

How to write a SUMIFS not equal to formula#

SUMIFS adds the numbers in the first range where every condition is met. The syntax is:

=SUMIFS(sum_range, criteria_range1, criteria1, ...)

For "not equal to", the criteria is <> followed by the value, all inside double quotes:

=SUMIFS(D2:D8,C2:C8,"<>Cancelled")

The result is 4,300: the full 5,400 minus the two cancelled orders (800 and 300). The SUMIFS function guide has the full syntax, and SUMIF vs SUMIFS explains why the sum range comes first here but last in SUMIF.

A few rules for the criteria:

• <> is Excel's not-equal sign. != from programming gives no match, so the formula adds everything.
• The operator and the value go together in one pair of quotes: "<>Cancelled", not "<>" "Cancelled".
• It is not case-sensitive: "<>cancelled" gives the same 4,300.

How to combine not equal with other conditions#

You can mix a "not equal" condition with normal ones. Every condition must be true for a row to be added. To total North orders that were not refunded:

=SUMIFS(D2:D8,B2:B8,"North",C2:C8,"<>Refunded")

North has three orders: 1,200 (Paid), 450 (Refunded) and 600 (Paid). The refund is left out, so the result is 1,800. This is the exercise on this page. The guide to COUNTIFS and SUMIFS with multiple conditions shows more combinations.

How to exclude two or more values with SUMIFS#

Repeat the same criteria range once for each value you want to leave out:

=SUMIFS(D2:D8,C2:C8,"<>Cancelled",C2:C8,"<>Refunded")

A row is added only if its status is not Cancelled and not Refunded. That leaves the four Paid orders: 1,200 + 950 + 600 + 1,100 = 3,850.

This is where SUMIFS beats SUMIF. SUMIF takes one condition, so excluding two values with it needs a subtraction trick. The SUMIF not equal guide shows that approach for older files.

The double-counting mistake#

It looks logical to add two "not equal" totals together. Don't:

=SUMIFS(D2:D8,C2:C8,"<>Cancelled")+SUMIFS(D2:D8,C2:C8,"<>Refunded")

This returns 9,250, far more than the 5,400 in the whole table. Each Paid order is "not Cancelled" and also "not Refunded", so it is counted twice. Adding formulas is how you get OR logic, which is the opposite of what you want here. See SUMIFS with OR logic for when adding is the right move.

How to use not equal to a cell value#

To exclude whatever is typed in a cell, keep the operator in quotes and join the cell with &. If G2 holds Cancelled:

=SUMIFS(D2:D8,C2:C8,"<>"&G2)

The result is 4,300, and it updates when you type a different status in G2. Writing "<>G2" instead would exclude the letters "G2", which match nothing, so you would get the full 5,400.

Not equal to a number, zero or blank#

The same <> works with numbers and empty cells:

"<>" on its own means "not empty". The wildcard version catches "Cancelled", "cancelled by customer" and similar, because * stands for any text.

Not equal to in COUNTIFS and AVERAGEIFS#

The <> criteria work the same way in the other "IFS" functions, because they all share the same criteria rules. Only the first argument changes:

COUNTIFS has no sum range, so it starts straight away with the criteria range. AVERAGEIFS and MAXIFS put the range of numbers first, like SUMIFS. Learning one set of criteria rules pays off across all of them.

Check your result with total minus excluded#

A quick way to check a "not equal" total is to subtract the excluded part from the grand total:

=SUM(D2:D8)-SUMIFS(D2:D8,C2:C8,"Cancelled")

That is 5,400 − 1,100 = 4,300, the same as the not-equal formula. If the two answers differ, look for text that only looks the same, such as "Cancelled " with a trailing space or "Canceled" with one L. Those cells don't match "Cancelled", so they slip through the exclusion. TRIM removes the stray spaces.

Other common causes of a wrong total:

• The ranges are different sizes. SUMIFS returns #VALUE! when D2:D8 and C2:C9 don't match. Make every range cover the same rows.
• You expected filtered rows to drop out. SUMIFS still adds rows hidden by a filter. Use SUBTOTAL or AGGREGATE when you need visible rows only.
• The criteria cell is empty. If G2 is blank, "<>"&G2 becomes "<>", which means "not blank", and every filled row is added.

If your conditions get more complex than SUMIFS can handle, such as excluding by part of a date or by case, SUMPRODUCT vs SUMIFS explains when to switch.

Practise it#

Practise COUNTIFS and SUMIFS in a live spreadsheet: you type each formula, including <> conditions, and it checks your answer instantly. Then try the North, not refunded exercise on this page to lock in the syntax.