A colleague of mine asked me today how to go about using MS Excel formulas to calculate the sum of absolute values from a given dataset. This question crops up quite often on the forums so I thought I’d put together a quick blog post on it. There isn’t a built-in `SUMABS()`

(or equivalent) worksheet function so you have to do a little bit of work to get the right answer.

So here’s our sample data:

We can see that the sum of A2:A8 would give a result of -60 but, if we were to consider the absolute values (the numbers without their – sign) then the result would be 180.

`= -10 + 10 + 20 + -20 + 30 + -40 + -50 = -60`

`= 10 + 10 + 20 + 20 + 30 + 40 + 50 = 180`

**Option 1 – Use A Helper Column**

In my opinion, the best way to do this is to use a helper column. In cell B2 put the formula `=ABS(A2)`

and then fill down to B8. The `ABS()`

worksheet function returns the absolute value, so now we can just take the sum of B2:B8 and it’ll give us a result of 180.

`=SUM(B2:B8)`

In my example, I’ve actually got A1:A8 set up as a table, so when I added the `=ABS(A2)`

formula into B2, Excel automatically expanded the table and filled the formula down the column for me. I then navigated to the Table Design tab on the ribbon, ticked the Total Row checkbox and it automatically inserted a formula to get the total:

The `SUBTOTAL()`

formula it inserted is just a fancy version of the `=SUM(B2:B8)`

formula I mentioned a minute ago. It gives exactly the same result – it sums the numbers – but it also ignores any numbers which have been hidden should you start to apply filtering or manually hide some of the table rows. There are a few other differences between them so have a read through the help file topic on `SUBTOTAL()`

if you’re unfamiliar with it.

I think the helper column approach is particularly good because it is simple and it gives a lot of flexibility if you want to use the data further – such as in a chart or in a pivot table. The helper column can also be used to sort the data by absolute values.

Okay, that’s all well and good, but what if you just want to do it in a single formula?

**Option 2 – Use A SUM() Array Formula Or SUMPRODUCT()**

Using an array formula or `SUMPRODUCT()`

is a brute force approach to get the job done.

`{=SUM(ABS(A2:A8))}`

`=SUMPRODUCT(ABS(A2:A8))`

Given that we can actually use a more efficient `SUMIF()`

formula to give the same result (covered in option 3), I’d suggest that this option is the one which generally shouldn’t be used. It’s true that these formulae are absolutely fine on a small dataset such as this, but when your data starts to get large and you have more and more heavyweight formulas in your worksheet, you’ll notice that it starts to slow down.

**Option 3 – Use SUMIF()**

I think this formula is pretty cute – it’s similar to the `SUMIF()`

approach to get a sum between two dates.

`=SUMIF(A2:A8,">0") - SUMIF(A2:A8,"<0")`

which can also be written as

`=SUM(SUMIF(A2:A8,{">0","<0"})*{1,-1})`

This formula takes the sum of the positive numbers and then subtracts the sum of the negative numbers. If we break down the first version:

`=SUMIF(A2:A8,">0") - SUMIF(A2:A8,"<0")`

evaluates to:

`=60 - -120`

which evaluates to:

`=180`

And, for completeness, if we break down the second version:

`=SUM(SUMIF(A2:A8,{">0","<0"})*{1,-1})`

evaluates to:

`=SUM({60,-120}*{1,-1})`

which evaluates to:

`=SUM({60,120})`

which evaluates to

`=180`

Well, I think that pretty much covers it. If you use a different approach to calculating the absolute sum then post a comment and share it with us.

Pingback: SUMIF Formulas | RAD Excel

Colin,

Nice article. There are many other ways that one could achieve the same outcome. For example, using an array formula like: {=SUM(SUBSTITUTE(A1:A6,”-“,””)*1)}

This works by treating the values as text and removing the negative sign (if there is one). It then multiplies by 1 to convert the text back to being a number, which we then sum.

Although my approach works, I would not recommend it. Similarly, I would not recommend your option 3, as it is obscure and hence likely to cause confusion and potentially errors when a later developer changes the spreadsheet. Options 1 and 2 are good, though.

BTW, I’ve added a link to this page in our collection of the best articles about Excel at http://www.i-nth.com/resources/connexion

Bob Watson.

LikeLike