Solved Keep a running total of a growing spreadsheet

Microsoft Office 2010 professional
February 20, 2014 at 06:01:43
Specs: Windows 7
So....a friend has a spreadsheet that will be growing daily with new data and on one row, he'll have the totals of all calls that he wants to sum automatically. Cell a2 has the year to date total but instead of using sum(a3:xx3) or something similar, he asked if there's an 'Infinite' or some other command that would catch it if his colums go to xy, he'd still have the correct total.

Is there any way this can be done?


See More: Keep a running total of a growing spreadsheet

Report •


#1
February 20, 2014 at 08:04:13
✔ Best Answer
There are two ways I can think of,
one is simply SUM the whole row like:

=SUM(A2:A65536) this is for Excel 2003
=SUM(A2:A1048576) this is for Excel 2007 + 2010

A much more elegant way is to use a Dynamic Named Range
Here are two examples:

http://www.excel-easy.com/examples/...
http://www.ozgrid.com/Excel/Dynamic...

MIKE

http://www.skeptic.com/


Report •

#2
February 20, 2014 at 08:28:34
Thanks, Mike! I like the simplicity of the first one.

Report •

#3
February 20, 2014 at 09:16:26
I like the simplicity of the first one

But you may give up speed for simplicity,
because you will be checking a LOT of cells that are empty.

I always advise against using the shortcut form of A:A to sum a whole column
this is basically the same thing, so I would advise you use the Dynamic Name Range solution. It may take a few seconds longer to set up, but in the long run it is the better of the two option. In my opinion.

MIKE

http://www.skeptic.com/


Report •
Related Solutions


Ask Question