complex sum

[#1]

I have a really complex problem. I need an expression which will enable me to monitor/supervise my buyers' payments, and how much they still owe me. Data base gives me these results in Excel table:

      Excel report sheet

BILL NAME     BILL AMOUNT     PAID

Bill1                    $100.000       $70.000

Bill1                    $100.000       $20.000

Bill1                    $100.000       $10.000

Bill2                      $50.000       $10.000

Bill2                      $50.000       $20.000

Bill 1 is $100.000                     Buyer owes $20.000

Bill 2 is $50.000                       Paid: $130.000

If I summarize the column: " Bill Amount", it would turn out that the buyer bought $400.000 of product, but i don't need that. I need it to add Bill 1 - $100.000, and bill2 -$50.000 only once, although it can appear more than one time in the table.

I know this is kinda complicated (messy), but I'm condemned to that report in Excel.

Thanks for your time

[Anonymous]

AGRR( sum( DISTINCT BILL AMOUNT) , BILL NAME )

[#3]

Aggr(Sum(DISTINCT ),[BILL NAME])-Aggr(Sum(PAID), ).

This shall give amount owed against each bill.

Regards,

K

On Aug 17, 2012 11:41 PM, "matoliki" <qliktech@sgaur.hosted.jivesoftware.com>