Sum Values Not Equal to Criteria Using SUMIFS in Excel

Effortlessly Summing Excel Values Not Equal to a Specific Criteria using SUMIF and SUMIFS Functions

In Excel, to sum values that are not equal to specific criteria, you can use SUMIF or SUMIFS functions depending on your data. This tutorial covers both scenarios.

Using Sum Values for Not Equal To Criteria:

If you have a list of values and want to sum all except those matching a specific criterion (e.g., 2000), you can use the following formula:

=SUMIF(A2:A14,”<>2000″,A2:A14)

Here’s how to use it:

– Enter “=SUMIF” in cell C1 to start the function

– Specify the range you want to check the criteria for (not equal to 2000)

– Enter the values you want to use as criteria, enclosing them in double quotation marks and using the not equal operator (sign)

– Specify the range from which you want to sum the values

– Hit enter
You will get the total of all values that are not equal to 2000 in the result (note that [sum_range] is optional).

Using SUMIFS for Multiple Not Equal To Criteria (Text):

If you need to sum values using multiple not equal to criteria (e.g., for products that are not A or C), you can use the following formula:

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

Here’s how to use it:

– Enter the formula in the cell where you want the result
– Specify the range from which you want to sum the values (B2:B13)
– Specify the criteria range and value for each criterion (e.g., for products not A or C)

– Hit enter

You will get the sum of all values that are not equal to A or C in the result.