# Absolute value sum???

This is a discussion on Absolute value sum??? within the Excel Questions forums, part of the Question Forums category; I create a monthly report that usually has 6 columns of data. They are as follows: Month Actual, Month Budget, ...

1. ## Absolute value sum???

I create a monthly report that usually has 6 columns of data. They are as follows: Month Actual, Month Budget, Month Variance, Year Actual, Year Budget, Year Variance. My goal is to eliminate any row who has a 0 balances across. Currently I reference the cells to the right in other columns and take their absolute values. Then I sum those lines. Any sum with a value of 0 can consequently be deleted.

The reason I have to use absolute values to sum a row instead of just taking the sum is as follows. Say a particular row has no month activity, so all 3 columns related to the month are 0. But the year actual has a balance of \$100 and the year budget has a balance of 0. Well the year variance would be -\$100 and a sum of the line would net a 0 balance. Therefore if I take the absolute values I ensure myself that I will not delete any rows that contain activity.

If anyone knows a function or how to right this into a macro I would be much appreciative. I think I currently have the problem solved by using Access with setting up each "Or:" criteria as <>0. However I would like to know a way to do this in excel. Thanks.

2. ## Re: Absolute value sum???

Assuming that your values are in columes A thru F, you could use this formula...
Code:
`=SUM(ABS(A1:F1))`
NOTE: This muse be entered as an array formula by using CTRL+SHIFT+ENTER.

3. ## Re: Absolute value sum???

Originally Posted by TommyGun
Assuming that your values are in columes A thru F, you could use this formula...
Code:
`=SUM(ABS(A1:F1))`
NOTE: This muse be entered as an array formula by using CTRL+SHIFT+ENTER.

At what point do I do the ctrl+shift+enter command? Sorry I'm a bit of a noob.

4. ## Re: Absolute value sum???

Originally Posted by smashclash
Originally Posted by TommyGun
Assuming that your values are in columes A thru F, you could use this formula...
Code:
`=SUM(ABS(A1:F1))`
NOTE: This muse be entered as an array formula by using CTRL+SHIFT+ENTER.

At what point do I do the ctrl+shift+enter command? Sorry I'm a bit of a noob.
Type the formula then confirm it with control+shift+enter instead of just with enter. Also, the following with just enter:

=SUMPRODUCT(ABS(A1:F1))

5. ## Re: Absolute value sum???

Know this is old however I would approach this way:

=SUMIF(Range,">0")-sumif(Range"<0")

No need for CSE

6. ## Re: Absolute value sum???

Originally Posted by IronOrchid
Know this is old however I would approach this way:

=SUMIF(Range,">0")-sumif(Range"<0")

No need for CSE
Good idea. There is a typo:

=SUMIF(Range,">0")-sumif(Range,"<0")

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•