SUMIF Not Equal To: Exclude a Value From a Total

SUMIF not equal to adds up every row except the ones that match a value. In Excel, "not equal to" is written <>, so =SUMIF(A2:A8,"<>North",C2:C8) totals the sales for every region but North. This guide shows the formula step by step, how to exclude a value from a cell or two values at once, how blanks behave, and the mistakes that give the wrong total.

How to write a SUMIF not equal to formula#

SUMIF adds the numbers in one range when the cells in another range meet a condition, called the criteria:

=SUMIF(range, criteria, [sum_range])

For "not equal to", the criteria is <> followed by the value, all inside double quotes. Here is the sales table used in every example, in A1:C8:

To total every region except North:

1. Click an empty cell, such as E2.
2. Type =SUMIF(A2:A8,"<>North",C2:C8).
3. Press Enter.

The result is 4050. That is 800 + 950 + 1100 + 700 + 500: every row except Ana's 1200 and Dan's 600. SUMIF ignores capitals, so "<>north" gives the same answer. For a full walk-through of the function, see how to use SUMIF.

Why <> means not equal to#

Excel has six comparison operators, and <> is the one for "not equal to". You can read it as "less than or greater than", which is another way of saying "different from".

The != from programming languages is a common slip. In a SUMIF criteria, "!=North" is treated as plain text, so Excel adds the rows where the region is literally "!=North", and the total is 0. The guide to Excel comparison operators lists them all, and the lesson on TRUE and FALSE comparisons lets you practise them.

How to sum if not equal to a cell value#

Put the value to exclude in a cell so you can change it without editing the formula. With South in E1:

=SUMIF(A2:A8,"<>"&E1,C2:C8)

The result is 4350: the total of 5850 minus South's 800 and 700. The & joins the operator to the text in E1, so Excel sees "<>South". Keep the operator inside quotes and the cell reference outside. Writing "<>E1" excludes the word "E1", which isn't in the data, so you'd get the full total of 5850.

How to exclude two or more values#

SUMIF takes one condition, so to leave out two values use SUMIFS with one <> condition for each, on the same range:

=SUMIFS(C2:C8,A2:A8,"<>North",A2:A8,"<>South")

The result is 2550: East (950 + 500) and West (1100). SUMIFS adds a row only when it meets every condition, so a row must be "not North" and "not South". Note that SUMIFS puts the sum range first, unlike SUMIF. The comparison of SUMIF vs SUMIFS explains the change in argument order.

You can't do this with OR logic. SUMIF(A2:A8,"<>North",C2:C8)+SUMIF(A2:A8,"<>South",C2:C8) adds 4050 and 4350 to get 8400, which is more than the whole column, because East and West are counted twice.

Not equal to a number, zero or blank#

The same operator works with numbers. To add every sale that isn't exactly 800:

=SUMIF(C2:C8,"<>800")

When the sum range is left out, SUMIF adds the cells it checks, so this gives 5050. A common use is "<>0" to skip zeros, for example when you also want to count or average only the non-zero rows.

"<>" with nothing after it means "not blank". =SUMIF(A2:A8,"<>",C2:C8) adds every row where column A has something in it, which is useful when some rows have no region yet.

That leads to one trap: a row with a blank region counts as "not equal to North", because blank is not North. If some rows have no region, "<>North" includes them. To leave out both North and blanks, use SUMIFS with "<>North" and "<>" as two conditions.

Check your result with the total-minus method#

A not-equal-to total is the same as the grand total minus the matching total. That gives you a quick way to check your work:

=SUM(C2:C8)-SUMIF(A2:A8,"North",C2:C8)

That is 5850 - 1800 = 4050, the same as the <> formula. If the two answers differ, the data probably has a near-match, such as "North " with a trailing space or "Nrth", that neither formula treats as North. Clean the column with TRIM, or fix the typos, and the totals will agree.

Why SUMIF not equal to gives the wrong total#

If the total looks off, check these:

1. Using != instead of <>. Excel only understands <>.
2. The cell reference is inside the quotes. Write "<>"&E1, not "<>E1".
3. Extra spaces in the data or criteria. "<> North" with a space after the operator is read as " North" with a leading space, so the North rows can end up in the total. Keep the criteria tight: "<>North".
4. Blank rows are included. Add a "<>" condition in SUMIFS to drop them.
5. The ranges are misaligned. In SUMIF, the check range and the sum range should start on the same row and be the same size.

The guide to SUMIF not working covers more causes. To exclude rows that contain a word rather than match it exactly, use a wildcard like "<>*test*"; see SUMIF contains text. For totals above or below a number, read SUMIF greater than.

Practise it#

The <> operator is a small detail that job tests love to check. Practise SUMIF in a live spreadsheet: you type the formula and it checks it instantly. The SUMIF function guide keeps the syntax and criteria examples on one page.