Cellwise Start free

Excel guide · 4 min read

How to use SUMIF not equal to in Excel

Quick answerUse =SUMIF(C2:C10,"<>Refunded",D2:D10) to add the amounts in D2:D10 whose status in column C is anything except Refunded. "<>" means "not equal to" and goes inside the quotes. To use a cell, join it with &: "<>"&G1. To leave out several values, give SUMIFS one "<>" condition per value.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFG
1OrderRegionStatusAmountLeave outRefunded
2SO-2201NorthShipped1200Leave out listRefunded
3SO-2202SouthRefunded450Cancelled
4SO-2203NorthShipped980
5SO-2204WestCancelled300
6SO-2205Southrefunded220
7SO-2206North640
8SO-2207WestShipped1500
9SO-2208SouthRefunded 375
10SO-2209NorthPending810

Download this sheet as an Excel practice file (.xlsx)

SUMIF with "<>"

The sheet is a month of sales orders with a region, a status and an amount. All orders add up to 6,475. To report sales without refunds, SUMIF tests the status column and adds the amounts of every row that is not Refunded.

The order is range to test, condition, then range to add. The match is not case-sensitive, so the lower-case "refunded" in row 6 is left out too. Rows with no status at all (row 7) are kept, because an empty cell is not equal to Refunded.

=SUMIF(C2:C10,"<>Refunded",D2:D10)

Result: 5805

5805: the total minus the 450 and 220 refunds. The 375 in row 9 is still included; see the space trap below.

Not equal to a cell

Put the value in a cell so you can change it without editing the formula. Keep "<>" in quotes and join the cell with &. Writing "<>G1" would compare against the letters G1, which matches every row.

Watch out when that cell is empty. "<>"&H1 with an empty H1 becomes just "<>", which means "not blank", so the formula quietly drops the rows with no status: =SUMIF(C2:C10,"<>"&H1,D2:D10) returns 5835, the 640 for the order without a status is gone.

=SUMIF(C2:C10,"<>"&G1,D2:D10)

Result: 5805

5805, the same as typing "<>Refunded", with Refunded in G1.

The space trap: "Refunded " is not "Refunded"

Row 9 says "Refunded " with a trailing space, typical of exported data. To Excel that is a different value, so "<>Refunded" keeps it and the 375 slips into the total. A wildcard catches it: "<>Refunded*" means "does not start with Refunded". "<>*refund*" leaves out anything that contains refund anywhere.

Wildcards also catch values you did not mean, such as "Refunded - partial". The cleaner fix is to clean the column with TRIM or Find & Replace, or test the trimmed value with SUMPRODUCT: =SUMPRODUCT(D2:D10*(TRIM(C2:C10)<>"Refunded")) also returns 5430.

=SUMIF(C2:C10,"<>Refunded*",D2:D10)

Result: 5430

5430: all three refunds (450, 220 and 375) are now left out.

Leave out several values with SUMIFS

SUMIF takes one condition, but SUMIFS takes many, and every condition must be true for a row to count. "Not Refunded" and "not Cancelled" on the same column is exactly that: a row passes only when it is neither. SUMIFS puts the range to add first.

Add a third pair, C2:C10,"<>", to also drop rows with no status (4865). Or subtract instead: =SUM(D2:D10)-SUMIF(C2:C10,"Refunded",D2:D10)-SUMIF(C2:C10,"Cancelled",D2:D10) gives the same 5505. Both work in any current Excel version.

=SUMIFS(D2:D10,C2:C10,"<>Refunded",C2:C10,"<>Cancelled")

Result: 5505

5505: everything except the 670 in refunds and the 300 cancellation.

Leave out a list of values

When the excluded values live in a list (G2:G3 here) that may grow, one SUMIFS pair per value gets long. COUNTIF can check each status against the list: COUNTIF(G2:G3,C2:C10) returns how many times each row's status appears in the list, and =0 keeps the rows that are not in it. SUMPRODUCT then adds their amounts.

Another way is to subtract the listed values: =SUM(D2:D10)-SUM(SUMIF(C2:C10,G2:G3,D2:D10)). Either way, extend G2:G3 to cover the whole list.

=SUMPRODUCT(--(COUNTIF(G2:G3,C2:C10)=0),D2:D10)

Result: 5505

5505: the same as the SUMIFS above, driven by the list in G2:G3.

Case-sensitive not equal, and other conditions

SUMIF and SUMIFS ignore case. If "refunded" in lower case means something different in your data, EXACT compares with case: =SUMPRODUCT(D2:D10*NOT(EXACT(C2:C10,"Refunded"))) leaves out only the exact "Refunded" and returns 6025.

To combine not-equal with a normal condition, add pairs to SUMIFS. North sales that were not refunded: =SUMIFS(D2:D10,C2:C10,"<>Refunded",B2:B10,"North") returns 3630. Numbers work the same way: =SUMIF(D2:D10,"<>300") adds every amount except 300.

Common mistakes

Writing "<>G1" instead of "<>"&G1

Inside quotes G1 is text, so nothing equals it and every row is added. Join the cell with &.

An empty criteria cell

"<>"&G1 with an empty G1 means "not blank" and drops rows with no value. Check the cell is filled, or wrap the formula in IF(G1="","",...).

Two values in one SUMIF

"<>Refunded,Cancelled" looks for that exact text. Use SUMIFS with one "<>" pair per value.

Hidden trailing spaces

"Refunded " is a different value. Clean the column with TRIM, or use the "<>Refunded*" wildcard if no other status starts with the same word.

FAQ

How do I sum if not equal to in Excel?

=SUMIF(C2:C10,"<>Refunded",D2:D10) adds D for every row whose C is not Refunded. Use "<>"&G1 to take the value from a cell.

How do I SUMIF not equal to multiple values?

Use SUMIFS with one condition per value on the same column: =SUMIFS(D2:D10,C2:C10,"<>Refunded",C2:C10,"<>Cancelled"). For a longer list, =SUMPRODUCT(--(COUNTIF(G2:G3,C2:C10)=0),D2:D10).

Does SUMIF not equal include blank cells?

Yes. An empty cell is not equal to Refunded, so its amount is added. Add C2:C10,"<>" as an extra SUMIFS condition to leave blanks out.

Is SUMIF not equal case-sensitive?

No, "<>Refunded" also leaves out "refunded". For a case-sensitive test, use SUMPRODUCT with EXACT.

How do I sum if a cell does not contain a word?

Use wildcards with "<>": =SUMIF(C2:C10,"<>*refund*",D2:D10) leaves out every status that contains refund.

Functions used

Related guides

Updated