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

ExcelTips Resources

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

**Solve Real Business Problems** Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out *Microsoft Excel 2013 Data Analysis and Business Modeling* today!

Today I've learned something I never knew - array formula's and CTRL-SHIFT-ENTER. I've been trying to find why the ABS func applied to a range of cells wasn't working for the last few days and after much searching, this is the answer. Thank you. Couldn't resolve in Microsoft help!

=sum(iif(position="driver" and "labour" and "civil enginner" and "project manager" and "manager",1,0))

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

help me

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

help me

@Sabdjong Tomi,

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

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

Dear Allen

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)

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)

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