Table of Contents
To calculate the sum of cells in Excel that are not equal to a specific value, you can use the SUMIF function. This function allows you to specify a range of cells and a criteria, and it will only sum the cells that meet that criteria. By using the criteria “not equal to” and entering the specific value you want to exclude, you can accurately calculate the sum of the remaining cells. This is a useful tool for data analysis and organizing large sets of data in Excel.
Excel: Calculate Sum If Cells Not Equal to Value
You can use the following formulas to calculate the sum of values in Excel for cells that are not equal to some value:
Method 1: Calculate Sum If Cells Not Equal to Value
=SUMIF(A1:A100, "<>value", B1:B100)
This formula calculates the sum of values in B1:B100 where the value in A1:A100 is not equal to value.
Method 2: Calculate Sum If Cells Not Equal to Several Values
=SUMIFS(B1:B100, A1:A100, "<>val1", A1:A100, "<>val2", A1:A100, "<>val3")
This formula calculates the sum of values in B1:B100 where the value in A1:A100 is not equal to val1 orval2 orval3.
The following examples show how to use each method in practice.
Example 1: Calculate Sum If Cells Not Equal to Value
Suppose we have the following data in Excel:
We can use the following formula to calculate the sum of values in the Points column where the Team column is not equal to A:
=SUMIF(A1:A100, "<>value", B1:B100)
The following screenshot shows how to use this formula in practice:
The sum of the cells in the Points column where the cell in the Team column is not equal to “A” is 132.
We can verify this by manually calculating the sum of the Points column for all cells where the Team column is not equal to “A”:
Example 2: Calculate Sum If Cells Not Equal to Several Values
Once again suppose we have the following data in Excel:
We can use the following formula to calculate the sum of values in the Points column where the Team column is not equal to A or B:
=SUMIFS(B2:B13, A2:A13, "<>A", A2:A13, "<>B")
The following screenshot shows how to use this formula in practice:
The sum of the cells in the Points column where the cell in the Team column is not equal to “A” or “B” is 54.
We can verify this by manually calculating the sum of the Points column for all cells where the Team column is not equal to “A” or “B”:
Sum of Points: 12 + 20 + 9 + 13 = 54.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How do I calculate the sum of cells if they are not equal to a specific value in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-calculate-the-sum-of-cells-if-they-are-not-equal-to-a-specific-value-in-excel/
stats writer. "How do I calculate the sum of cells if they are not equal to a specific value in Excel?." PSYCHOLOGICAL SCALES, 30 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-calculate-the-sum-of-cells-if-they-are-not-equal-to-a-specific-value-in-excel/.
stats writer. "How do I calculate the sum of cells if they are not equal to a specific value in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-calculate-the-sum-of-cells-if-they-are-not-equal-to-a-specific-value-in-excel/.
stats writer (2024) 'How do I calculate the sum of cells if they are not equal to a specific value in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-calculate-the-sum-of-cells-if-they-are-not-equal-to-a-specific-value-in-excel/.
[1] stats writer, "How do I calculate the sum of cells if they are not equal to a specific value in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I calculate the sum of cells if they are not equal to a specific value in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.