Calculate The Absolute Sum in Excel

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:

ABSSUMSampleData

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:

ABSSUMHelperColumn

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.

ABSSUMPRODUCT

{=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})

ABSSUMIF

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.

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel, Microsoft Office and tagged , , , , , , . Bookmark the permalink.

2 Responses to Calculate The Absolute Sum in Excel

  1. Pingback: SUMIF Formulas | RAD Excel

  2. Bob Watson says:

    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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s