How can I use a formula to calculate a running sub-total in excel
 

I have a very large spreadsheet with customer names in column E, Sales in
column J and invoices issued in column K. I need to have a running total in
column L. The formula =sum(J$3:j3)-sum(k$3:k3) copied down provides a
running total of the net of column J and K however I need the starting
position of the running total to restart each time there is a change of
customer name in column E. For example, if there is a change of customer
name at row 25 the formula needs to be reset to =sum(J$25:j25)-sum(k$25:k25)
and to continue until the next change in customer name.

How can I use a formula to calculate a running sub-total in excel
 

Hi

For row 3, enter the formula

=IF($E3="","",SUMIF($E$3:$E3,$E3,$J$3:$J3)-SUMIF($E$3:$E3,$E3,$K$3:$K3))

, and cpoy down. Now you can forget about keeping customers together -
simply enter new entry at end of table regardless of customer, and running
sums for every customer are calculated over the table. To display specific
customer data, use autofilter feature.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"LossManiac" wrote in message
...
I have a very large spreadsheet with customer names in column E, Sales in
column J and invoices issued in column K. I need to have a running total
in
column L. The formula =sum(J$3:j3)-sum(k$3:k3) copied down provides a
running total of the net of column J and K however I need the starting
position of the running total to restart each time there is a change of
customer name in column E. For example, if there is a change of customer
name at row 25 the formula needs to be reset to
=sum(J$25:j25)-sum(k$25:k25)
and to continue until the next change in customer name.

How can I use a formula to calculate a running sub-total in ex
 

Thanks very much. Formula now input and tested.

Regards

"Arvi Laanemets" wrote:

Hi

For row 3, enter the formula

=IF($E3="","",SUMIF($E$3:$E3,$E3,$J$3:$J3)-SUMIF($E$3:$E3,$E3,$K$3:$K3))

, and cpoy down. Now you can forget about keeping customers together -
simply enter new entry at end of table regardless of customer, and running
sums for every customer are calculated over the table. To display specific
customer data, use autofilter feature.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"LossManiac" wrote in message
...
I have a very large spreadsheet with customer names in column E, Sales in
column J and invoices issued in column K. I need to have a running total
in
column L. The formula =sum(J$3:j3)-sum(k$3:k3) copied down provides a
running total of the net of column J and K however I need the starting
position of the running total to restart each time there is a change of
customer name in column E. For example, if there is a change of customer
name at row 25 the formula needs to be reset to
=sum(J$25:j25)-sum(k$25:k25)
and to continue until the next change in customer name.