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

**Your Data, Your Way!** Want the greatest control possible over how your data appears on the page? Excel's custom formats can provide that control, and *ExcelTips: Custom Formats* can unlock the secrets to creating your own custom formats. Check out *ExcelTips: Custom Formats* today!

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form below!)