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

  • Thread starter Thread starter LossManiac
  • Start date Start date
L

LossManiac

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.
 
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.
 
Back
Top