How to sum the absolute values in Excel?
Supposing you have a list of data which contains both positive numbers and negatives, and now you want to sum their absolute values which means all the negatives will be calculated as positives. For example, you want 10+(-10) would be 20 instead of 0. Are there any quick tricks to help you calculate the absolute values in Excel?
- Sum the absolute values with formulas
- Sum the absolute values by changing all negative numbers to positive in Excel
- Sum the absolute values an amazing tool
Here are some cute formulas for you to sum the absolute values, please look at this:
Formula 1: SUMIF() function
With the following two SUMIF functions, you can quickly calculate the sum of the absolute values, do as this:
1. In a blank cell which you want to put the result, enter one of below formulas, and press the Enter key.
=SUMIF(A2:A14,">0") - SUMIF(A2:A14,"<0")
Formula 2: SUMPRODUCT() function
This Sumproduct formula also can do a favor for you, please type the following formula, and press Enter key, then you will get the sum of the absolute values that you need.
Formula 3: SUM() Array Formula
If you are interested in the array formula, I can talk about this Sum array formula. Please enter below formula, and press Ctrl+Shift+Enter keys to get the sum.
Apart from formulas, we can change all negative numbers to positive ones and then sum up these numbers. And you can apply Kutools for Excel's Change Sign of Values utility to get it done with ease.
1. Select a blank cell below the numbers whose absolute values you will add, type the formula =SUM(A2:A14) (A2:A14 is the number cells whose absolute values you will sum) into it, and press the Enter key. See screenshot:
2. Select these number cells (A2:A14 in our case), and click Kutools > Content > Change Sign of Values.
3. In the opening Change Sign of Values dialog box, please check the Change all negative values to positive option, and click the Ok button.
You can also apply the Sum Absolute Value formula of Kutools for Excel to solve the problem easily.
1. Select a blank cell you will place the sum at, and click Kutools > Formula Helper > Formula Helper.
2. In the Formula Helper dialog, please select Math from the Formula Helper drop-down list, click to highlight Sum absolute values in the Choose a formula list box, specify the range of numbers whose absolute values you will sum, and click the Ok button.
Now you get the sum of selected numbers' absolute values.