Sum Dynamic Range

[T86157]

Cell C1 should show the sum. Data will be entered in below, in rows. Is there a way to define or make it so that I do not have to specify a specific range to sum since data will be constantly entered? I know I could probably do something like C:C but I noticed when I do this for several equations or sums, it starts to slow down my excel tremendously. I was wondering if there is a more efficient way of summing data.

[jeffreybrown]

This is a dynamic way to sum column C depending on growing data without using the =SUM(C:C)

=SUM(C1:INDEX(C:C,MATCH(9.99999999999999E+307,C:C)))

[dilipandey]

Hi T86157,

You could use a dynamic name to achieve the sum, see the attachment:-



dynamic range sum.xlsx
Regards,
DILIPandey

<click on below 'star' if this helps>

[Fotis1991]

Ηι

Another way, could be this.

In D1 for example type the Cell that you want formula stop count For example C552.

Then use this formula.

=SUM(C2:INDIRECT(D1))

Is this, works for you?

[T86157]

In regards to the 2nd and 3rd post, are these more efficient in terms of processing as well? I.e. less time to count and sum the data rather than sum entire columns

[jeffreybrown]

I'm not 100% sure on this question, but I do know, there are certain formulas which will count the entire range when using whole columns, but some are not this way.

Here is a great reference for preparing your spreadsheet for efficiencies created by Charles Williams

http://www.decisionmodels.com/index.htm

[Pepe Le Mokko]

Perhaps =SUM(C1:INDEX(C:C,COUNTA(C:C)))

INDIRECT being volatile, I would not recommend it

[T86157]

Perhaps =SUM(C1:INDEX(C:C,COUNTA(C:C)))

INDIRECT being volatile, I would not recommend it

This does not work if I want the sum to be in C1. It creates a circular reference.

[Pepe Le Mokko]

Typo should be =SUM(C2:INDEX(C:C,COUNTA(C:C)))

[T86157]

Oops, forgot to mention that. I tried changing it to C2 prior to you saying that, and that created a circular reference too. I think it has to do something with C:C being the whole column....

[jeffreybrown]

Post #2 should fix what you need...

Just change the C1 to C2

=SUM(C2:INDEX(C:C,MATCH(9.99999999999999E+307,C:C)))