calculate absolute values for each date (group sum)

Hi there,

I have the following simple problem setup for two columns let's say A and B:

01-02-2011 -534
01-02-2011 53
01-02-2011 43
02-02-2011 25
02-02-2011 -3523
02-02-2011 532
02-02-2011 3

I need to calculate the absolute value for each date in the summing of column B.

for example if u sum the whole column B as it is above then you get -3401. What I want is not to get rid of this minus-sign. But i want to get rid of the minus for each date-sum, for instance:

01-02-2011 -534
01-02-2011 53
01-02-2011 43

the output/number i want from this is abs(-534+53+43)=438

then for

02-02-2011 25
02-02-2011 -3523
02-02-2011 532
02-02-2011 3

abs(25-3523+3253+532+3)=2963

so the total is 2963+438=3401

can any help? ... should i use for-each construction or is it not simpler??

thanks.

Cedicon,

So that we can get it right the first time:

Where do you want the abs formula/result to be located in reference to each group?

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

For most efficient way, you can add a helper columns.. so say your data is in A1:B7

In C1 enter:

=IF(A2=A1,"",ABS(SUMIF(A:A,A1,B:B)))

copied down

Then to get total Sum: =SUM(C:C)

Edit: This also assumes dates are grouped together...

Since you are using XL2007 or later, try with SUMIFS, which allows multiple criteria...

In H2:

=IF(AND(A3=A2,B3=B2),"",ABS(SUMIFS($C$2:$C$17,$A$2:$A$17,A2,$B$2:$B$17,B2)))

copied down