Dynamic Running Total?

[Eloise T]

Each week I add 100 to 200 rows of new data to the bottom of an ever-growing spreadsheet that’s currently over 12,000 rows long by 10 columns wide.

Column H contains a number that I need to maintain a running total.

I keep the running total at the top of the spreadsheet “inside” the top “frozen pane” to make it visible without having to scroll to the bottom over 12,000+ rows down.

Currently the formula I use is as simple as =SUM( H5 : H12357 )

I’m sure I could run the second parameter to a huge number and increase it as needed, but is there a way to improve the formula to dynamically change when rows are added to maintain the running total of the entire Column H?

 

[Nebu]

Hi:

Use the following formula

=SUM(OFFSET(H5,,,COUNT(H5:H1048576),1))

Thanks

 

[Khalid NGO]

Hi Eloise T,

Or you can convert your data into Table, and use:

=SUM(Table1[Cost])

Regards,

 

[shrivallabha]

One more way :
=SUM(H5:INDEX((H:H),MATCH(9E+307,H:H)))

 

[Eloise T]

Hi:

Use the following formula

=SUM(OFFSET(H5,,,COUNT(H5:H1048576),1))

Thanks

Thank you. Now I have to figure out why it works! ...as well as the formula created by shrivallabha and method created by Khalid NGO!

 

[Eloise T]

One more way :
=SUM(H5:INDEX((H:H),MATCH(9E+307,H:H)))

Thank you. Now I have to figure out why it works! ...as well as the formula created by Nebu and method created by Khalid NGO!

 

[Eloise T]

Hi Eloise T,

Or you can convert your data into Table, and use:

=SUM(Table1[Cost])

Regards,

What is the procedure to convert the data in Column H into a table?
I've started it in the attached file.

 

[Khalid NGO]

How would I convert the data in Column H into a table?

Make a Table of your complete range...

Select your range A4:H4 or even a single cell A4, go to Insert > Table.

Excel will automatically select your range.
Check mark "My table has headers"

Regards,

 

[Khalid NGO]

What is the procedure to convert the data in Column H into a table?
I've started it in the attached file.

I posted before you edited...

I think you should make a complete range Table i/o single column.

Table option > Resize Table > Change the Range =$A$4:$H$7 (adjust accordingly)

and note: Your Table Name is "Table3" not "Table1"

=SUM(Table3[Cost])

Regards,

 

[Eloise T]

I posted before you edited...

I think you should make a complete range Table i/o single column.

Table option > Resize Table > Change the Range =$A$4:$H$7 (adjust accordingly)

and note: Your Table Name is "Table3" not "Table1"

=SUM(Table3[Cost])

Regards,

How did you know it is Table 3 vs. Table 1? Where is it shown?

 

[Khalid NGO]

How did you know it is Table 3 vs. Table 1? Where is it shown?

Its here
Table Tools > Table Name


Regards,

 

[Eloise T]

You must have a different version of Excel. I don't have a Design tab in 2007.
I'll keep looking...

 

[Khalid NGO]

You must have a different version of Excel. I don't have a Design tab in 2007.
I'll keep looking...

Same version here...

Click on any cell which are in Table.

 

[Eloise T]

Amazing! Clever those Chinese! I learn something new every day! In fact, the more I learn in Excel, the more I realize how little I know about it.


...and Thank YOU!