**Please Note: **
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), *this tip may not work for you*. For a version of this tip written specifically for later versions of Excel, click here: Summing Absolute Values.

Joseph has a worksheet that contains a list of values. Some of those values are above zero and others are below. He can use the SUM function to calculate a sum of the values, but he really wants to calculate a sum of the absolute value of each item in the list. So, the sum of the three values -33, 14, -5 would be 52 instead of -24.

There is no intrinsic function you can use to create the desired sum, but you can create a formula to perform the task. One method is to use the SUMIF function, in the following manner:

=SUMIF(A1:A10,">0")-SUMIF(A1:A10,"<0")

The first SUMIF sums all the values that are greater than zero, and the second sums all those less than zero. Thus, with the four values -33, 14, -5, 42, the first SUMIF would result in a sum of 56 (14 + 42) and the second would result in a sum of -38 (-33 + -5). When you subtract the second sum from the first (56 - -38) you get a final answer of 94, which is the sum of all the absolute values.

Another approach is to use the SUMPRODUCT function. The following formula will produce the desired result:

=SUMPRODUCT(ABS(A1:A10))

The function is typically used to multiply different elements of arrays by each other, and then sum those products. Since only one array (A1:A10) is provided, there is no multiplication done, but a sum of the desired absolute values is returned.

You can also get the desired result by using an array formula, a convenient but seldom used feature of Excel. Assuming your values are in the range A1:A10, type this formula:

=SUM(ABS(A1:A10))

Don't press **Enter**; instead press **Ctrl+Shift+Enter**, which signifies this is an array formula. If the formula is entered correctly, you'll see braces around the formula in the Formula bar:

{=SUM(ABS(A1:A50))}

What the formula does is internally create the intermediate column (which is an array of values) which are the individual absolute values of A1:A10. It then sums this array and displays the result.

Finally, if you prefer you could create your own user-defined function (a macro) that will return the sum of the absolute values in a range. The following is a macro that will accomplish this task:

Function SumAbs(Rng As Range) As Double Result = 0 On Error GoTo Done For Each element In Rng Result = Result + Abs(element) Next element Done: SumAbs = Result End Function

You can use the function by entering a simple formula in your worksheet:

=SumAbs(A1:A10)

*ExcelTips* is your source for cost-effective Microsoft Excel training.
This tip (2913) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: **Summing Absolute Values**.

**Excel Smarts for Beginners!** Featuring the friendly and trusted *For Dummies* style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out *Excel 2013 For Dummies* today!

You can use some of Excel's worksheet functions across a range or worksheets, but not all of them. One that has problems with ...

Discover MoreIf you have a series of values in a range of cells, you may wonder how many of those values are even and how many are odd. ...

Discover MoreConvert a numeric value to text and you may be surprised by how Excel displays the value. Here's a run-down on exactly what ...

Discover More**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

2017-02-19 08:15:14

Katalin Tamas

Fantastic, thank you!

2017-01-05 08:37:04

Annette

Took just a few seconds to get the answer I needed and would never have figured out on my own!

2016-09-22 06:36:20

Dan

2016-02-25 08:10:09

khalid

i need total for every position in finally is separated not total for No of.positions.

help me

2015-08-14 05:48:55

Michael (Micky) Avidan

Please present your data table OR much better would be to upload your Workbook to a File Hosting site and let us have the link to download it.

*** Please explain - exactly - what your are trying to calculate

-----------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

2015-08-14 04:24:53

Sabdjong Tomi

is this forula correct to compte the average of absolute value in a range of data?

=+IFERROR((((SUM((ABS((C5-D5)/C5)),ABS((C6-D6)/C6),ABS((C7-D7)/C7),ABS((C8-D8)/C8),ABS((C9-D9)/C9),ABS((C10-D10)/C10),ABS((C11-D11)/C11),ABS((C12-D12)/C12),ABS((C13-D13)/C13),ABS((C14-D14)/C14),ABS((C15-D15)/C15),ABS((C16-D16)/C16),ABS((C17-D17)/C17),ABS((C18-D18)/C18),ABS((C19-D19)/C19),ABS((C20-D20)/C20),ABS((C21-D21)/C21),ABS((C22-D22)/C22),ABS((C23-D23)/C23),ABS((C24-D24)/C24),ABS((C25-D25)/C25),ABS((C26-D26)/C26),ABS((C27-D27)/C27),ABS((C28-D28)/C28),ABS((C29-D29)/C29)))/COUNTIF(D5:D29,"<>"))),0)

2015-07-29 16:36:39

John

That was awesome, thank you!

2015-02-09 10:07:01

Robert

@ Micky -

Forgive the double email - my coworker was able to get it to work.

Thank you!

Robert

2015-02-09 10:05:29

Robert

Thank you for the prompt reply.

I tried to use your formula and I was not able to get it to work.

I tried:

=SUMPRODUCT((I1:I336="W")*ABS(E1:E336))

and got an "#VALUE!" error.

2015-02-09 06:00:56

Michael (Micky) Avidan

If I understood you correctly and assuming you have:

A1:A10 with TickMarks

B1:B10 With Pos. & Neg. values ) in order to sum the absolute values that have a "W" in the adjacent cell - try the following formulas:

=SUMPRODUCT((A1:A10="W")*ABS(B1:B10))

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

2015-02-09 00:51:40

Robert

Specifically, I have a table of data with both positive and negative values. I'd like to perform a SUM IF, in absolute value, on a portion of the tables' data, indicated by a single column Tickmark?

For example, I'd like to SUM IF "W" in a column)

So I'm thinking of somehow creating a SUM IF = (indicate the "W", add your SUM IF to get absolute value)?

2014-09-11 16:18:38

Thomas

Got a version of Excel that uses the
menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)?
**This site is for you!** If you
use a later version of Excel, visit
our *ExcelTips* site focusing on the ribbon interface.

**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

Copyright © 2017 Sharon Parq Associates, Inc.

## Comments