Contents
A colleague of mine once asked me how to use Excel formulas to calculate the sum of absolute values in a given range. This question pops up quite regularly on the forums, and many users often have great difficulty with this seemingly simple action.
Unfortunately, there is no built-in function in Microsoft Excel that can perform modulo sums, so you have to work a little to get the correct answer.
Here is our data:
We see that the sum of the numbers in the range A2: A8 gives the result -60:
= -10 + 10 + 20 + -20 + 30 + -40 + -50 = -60
If we considered absolute values (numbers without the “-“ sign), then the result would be 180:
= 10 + 10 + 20 + 20 + 30 + 40 + 50 = 180
Option 1 – Using an Auxiliary Column
In my opinion, the best way to calculate the sum of absolute values in Excel is to use a helper column. To cell B2 enter the formula:
=ABS(A2)
Then we stretch it to the cell B8… Function ABS returns the modulus of the number. So now we can just sum the range B2: B8 and this will give us the result 180.
=СУММ(B2:B8)
=SUM(B2:B8)
In my example the range A1: A8 is a complete datasheet. Therefore, when adding the formula =ABS(A2) into a cell V2, Excel expanded the table and automatically filled in all the cells in the column. Next, I went to the tab Constructor (Design), which is in the tab group Work with tables (Table tools), and check the box next to the option Total row (Total Row). All values in a column B were automatically summed up, and the result was displayed in a separate line.
To calculate the sum in the total row, use the function SUBTOTALS (SUBTOTAL). This is a generic function that can perform summation, just like the function SUM (SUM). But there are also significant differences, for example, SUBTOTALS (SUBTOTAL) completely ignores numbers that have been hidden manually or by filtering. There are a few more differences, but this is of little relevance to the topic of this article.
The helper column method is nice because it gives you more flexibility if you need to use the data later, for example, in the form of a table or a pivot table. In addition, an auxiliary column can be used to sort numbers modulo.
This is no doubt a very good way, but what to do when you need to fit everything into one formula without any auxiliary columns?
Option 2 – Using the SUM function in an array formula or SUMPRODUCT
Use an array formula or SUMPRODUCT (SUMPRODUCT) to solve such a problem is a very rough approach!
Function SUM in an array formula:
=СУММ(ABS(A2:A8))
=SUM(ABS(A2:A8))
When entering an array formula, remember to press Ctrl + Shift + Enter.
Formula with SUMPRODUCT:
=СУММПРОИЗВ(ABS(A2:A8))
=SUMPRODUCT(ABS(A2:A8))
Given that it is possible to use a more efficient function SUMMESLI (SUMIF) to get the same result (see option 3), the path using these two formulas becomes deprecated. Both formulas work great with small data ranges, I’m sure you won’t even feel the difference. If you need to sum a large number of values, the speed of work will noticeably slow down.
Option 3 – Using SUMIF
I think that this approach is the most convenient of all previously listed. Using the function SUMMESLI (SUMIF) values are divided into 2 arrays: with positive and negative numbers and summed up. Zero is ignored for obvious reasons. Then we simply subtract the negative sum from the positive sum (i.e., sum them up). The formula looks like this:
=СУММЕСЛИ(A2:A8,">0")-СУММЕСЛИ(A2:A8,"<0")
=SUMIF(A2:A8,">0")-SUMIF(A2:A8,"<0")
It can also be written in this form:
=СУММ(СУММЕСЛИ(A2:A8,{">0","<0"})*{1,-1})
=SUM(SUMIF(A2:A8,{">0","<0"})*{1,-1})
If we take the first version of the formula, we get the following:
= 60-(-120) = 180
To complete the picture, we calculate the amount using the second option:
= СУММ({60,-120}*{1,-1}) = СУММ({60,120}) = 180
I think that now you know all the basic ways to calculate the sum of absolute values in Excel. If you use a different approach for modulo summing numbers, please share it in the comments. Good luck!