Running total formula?

[Malthus101]

Hi

Easy one here i'm sure - I have a column which I add new data to at the bottom.

I want a formula in a cell at the top (freeze pane) that will keep a running total as I add new numbers.

A SUM formula of some sort I suppose... but how to tell it to keep the addition going as I add them?

Thanks.

 

[alansidman]

Convert your range of data to a table. Every time you add a new line in a table it will automatically add those values to the Sum function.

 

[dreid1011]

You can use a range in the formula large enough to cover any amount of data you may ever add.

a large range

=SUM(A1:A9999)

or the entire column

=SUM(A:A)

or a dynamic named range that auto expands: Dynamic Named Range in Excel

or convert your range to a table

 

[Malthus101]

You can use a range in the formula large enough to cover any amount of data you may ever add.

a large range

=SUM(A1:A9999)

or the entire column

=SUM(A:A)

or a dynamic named range that auto expands: Dynamic Named Range in Excel

or convert your range to a table

I tried the entire column thing but get an error message:

 

[alansidman]

with the entire range, you are probably including the total cell in the calculation. Try the table solution and see if that works for you

 

[Malthus101]

with the entire range, you are probably including the total cell in the calculation. Try the table solution and see if that works for you

hi - yes that's what's happening. maybe I'll dedicate a cell that's not in any of the columns to keep a running total instead as I don't really want to use tables. Thanks.

 

[alansidman]

Changing the location will work, but out of curiosity what do you have against tables? They keep formatting and are easy to manipulate.

 

[Malthus101]

Changing the location will work, but out of curiosity what do you have against tables? They keep formatting and are easy to manipulate.

I don't know, they just seem sort of superfluous! I mean, isn't excel itself just a giant table? I would have thought Excel would know people might want a running and updating total and have made some function to provide that! Seems strange to me...

 

[alansidman]

Tables are structured to allow uniformity. Otherwise, you just have ranges of data. There is a difference. But as your needs, either way will work for you. Thanks for your response. Good Luck.

BTW An interesting read Excel Tables