Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Having a formula provide a running total from multiple worksheets | Excel Worksheet Functions | |||
Formula Needed to Compare Dates and return a running total | Excel Worksheet Functions | |||
Running total formula | Excel Worksheet Functions | |||
Formula for running total | Excel Worksheet Functions | |||
keep a running total of my formula results after each (F9) | Excel Discussion (Misc queries) |