Sum up all amount and subtract with receivable to show Net Total

Hai,

I have excel file consist of all sales invoices with different client name.
In same file there is summary sheet consist of all client name with column "Net Receivable".

Now I need to drive the "Net Receivable" from main sheet by sum up all
Invoice amount and minus with all received amount for each client and to
show the total in Net receivable column("C").

Can anyone help me please.
Lots of THANKS in advance.

Regards
Charles

Hi,

You could use this formula in C7 and fill down the column:

Please Login or Register  to view this content.

I think another neat way of doing this is to use a pivot table with a calculated field. The advantage of the pivot table is is will automatically derive the distinct list of client names for you. I've attached an example showing both ways.

What the pivot table tells me is that:

• You've missed off a couple of clients from your current summary list: GULF STEEL WORKS and ZAMIL Offshore.
• You've duplicated a couple of clients in your current summary list: SUNGCHANG and STFA Marine Construction Company .

(Note: You can exclude "Total" from the pivot table by using the filter on the Client Name, but I probably wouldn't include a total on the "Other" tab at all - you can do it in the "Summary" tab instead.)