SUMIFS Not Equal in Excel (How to Use It in 2024)
In this article, we will show you how to use SUMIF not equal in Excel. Simply follow the steps below.
SUMIF Not Equal to in Excel
To sum values not equal to a specific criterion, use the <> (not equal to) operator in SUMIF. This is useful for businesses managing inventory, excluding items out of stock or with specific characteristics.
Syntax
Here’s the basic syntax for using SUMIF with the not equal to operator:
=SUMIF(range, criteria, sum_range)
where:
range: This is the range of cells that you want to evaluate based on the criteria.
criteria: This is the condition that you want to apply to the cells in the range.
sum_range: It specifies the actual cells that you want to add up if they meet the criteria specified in the first argument (range).
How to Use SUMIFS Not Equal To in Excel
To sum values not equal to a specific criterion in Excel, you can use the SUMIFS function. Here’s how to do it:
1. Select the Cell Where the Result Will Appear
In our example, we have a list of electronic items from cell A2 to A9, each paired with their quantities in cells B2 to B9. You want to calculate the total quantity of all items except for “Smartphones.”
Click on the cell where you want the result of the `SUMIF` function to appear. This is typically a cell where you want the sum of quantities for specific criteria to be displayed. In this case, let’s choose B11
2. Begin Writing the `SUMIF` Formula
In the selected cell (B11), start typing the `SUMIF` formula. The formula should look like this:
=SUMIF(A2:A9, “<>Smartphone”, B2:B9)
This formula calculates the sum of quantities for electronic items that are not “Smartphone.”
3. Press Enter to Calculate
Press the Enter key to execute the formula. Excel will now calculate the sum of quantities for electronic items that are not “Smartphone” and display the result in the selected cell.
We hope that you now have a better understanding of how to use the function SUMIFS not equal in Excel. If you enjoyed this article, you might also like our article about using Excel SUMIFS for values less than a date or our article on how to use Excel SUMIF for cells that are not blank.
