Loading

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.

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.

Learn more about Allen...

ExcelTips FAQ

Ask an Excel Question

Make a Comment

Free Business Forms

Free Calendars

** 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),

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.

*Related Tips:*

**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!

That was awesome, thank you!

@ Micky -

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

Thank you!

Robert

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

Thank you!

Robert

@ Micky

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.

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.

@Robert,

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

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

Is there a way to incorporate the =SUMIF(A1:A10,">0")-SUMIF(A1:A10,"<0") with an additional SUM IF?

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)?

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)?

This was the tip I needed. I was dealing with a negative number in format (35), and the formula handled the number format just fine. Thanks