Assuming that your values are in columes A thru F, you could use this formula...NOTE: This muse be entered as an array formula by using CTRL+SHIFT+ENTER.Code:=SUM(ABS(A1:F1))
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.
Assuming that your values are in columes A thru F, you could use this formula...NOTE: This muse be entered as an array formula by using CTRL+SHIFT+ENTER.Code:=SUM(ABS(A1:F1))
Originally Posted by TommyGun
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:Originally Posted by smashclash
=SUMPRODUCT(ABS(A1:F1))
Know this is old however I would approach this way:
=SUMIF(Range,">0")-sumif(Range"<0")
No need for CSE
Like this thread? Share it with others