Sum Not Equal Values (SUMIFS)

In Excel, when you need to sum values that are not equal to specific criteria, you can use SUMIF or SUMIFS. Both functions can be used according to the data you have. In this tutorial, we will look at both situations.

SUMIF Not Equal To

In the following example, we have a list of values where we have three values that are 2000, but we need to sum all the other values.

Here we will use SUMIF to get the result.

=SUMIF(A2:A14,"<>2000",A2:A14)

1. First, in the C1, enter the “=SUMIF” and then enter the starting parentheses.
   
2. Next, select the range where we need to check the criteria (not equal to 2000).
   
3. After that, enter the values that we want to use as criteria i.e., not equal to 2000, and for this, we need to use the not equal operator (sign) and enclose it in double quotation marks.
   
4. From here, you need to specify the range from where we need to sum the values. Now as we have only one range that we have used, and the same range we need to use it the get the sum of values.
   
5. In the end, enter closing parentheses and hit enter.
   

As you can see, we have 16046 in the result which is the total of all the values which are not equal to 2000.

Note: [sum_range] is optional, you can skip specifying it and it will still return the total in the result.

=SUMIF(A2:A14,"<>2000")

SUMIFS Not Equal to Multiple Values (Text)

Let’s say you need to sum values using multiple, not equal values. In the following example, we have the list of products with the quantity, and you need to sum for the products that are not equal to A and C.

You can use the following formula.

=SUMIFS(B2:B13,A2:A13,"<>A",A2:A13,"<>C")

In the above formulas, we have used the multiple not equal criteria to get the sum of the rest of the values.