Summing a variable number of cells

**Bezzie** · 03-06-2014, 03:48 AM

I want to sum a number of cells. However; The number of cells vary from one run to the next. Is there a way to make the formula sum for instance Column D from row 7 to last row with a value in it? The rows can grow on every run but not necessarily. Or do I have to sum from row 7 to row last every time?

**XOR LX** · 03-06-2014, 03:54 AM

Hi,

Providing your entries in column D are entirerly numerical, this will sum from D7 to whatever happens to be the last cell in that range containing a number:

=SUM(D7:INDEX(D:D,MATCH(70^50,D:D)))

Regards

**:) Sixthsense :)** · 03-06-2014, 04:08 AM

May be...

=SUM(D7:D1048576)

**Bezzie** · 03-09-2014, 03:39 AM

Sixthsense: This is how I'm doing it at the moment. Was just wondering if there was a way to sum only to where the records end. Trying to squeeze as much speed out of it as possible but just summing a 1000000 cells is probably still faster then first having to calculate where to stop and then to sum up to there as XOR suggested.

**Bezzie** · 03-09-2014, 03:41 AM

XOR: Not all entries in the column will be numeric but once the numbers start there will be just numeric or blank cells.

**XOR LX** · 03-09-2014, 07:00 AM

But you seemed to indicate that it would always begin at D7, and that it was only the last cell which needed to be determined dynamically, which is what my formula does.

Are you now saying that both the first and last range references need to be determined dynamically?

Regards

**Bezzie** · 03-09-2014, 09:49 AM

My bad. Yes, it will start at the same row. Above that is column headings etc. But there could be blank cells and/or zero values in between.

**XOR LX** · 03-09-2014, 09:52 AM

That shouldn't matter. Can you give an example of a data sample where my formula did not give you the expected result?

Regards

**Bezzie** · 03-09-2014, 10:00 AM

Didn't say it wouldn't give the correct result. Just said it would probably be just as quick to sum the whole range as it would be to evaluate the formula with two additional functions Index() and Match() to determine where to stop summing. I'm trying to squeeze every second I can out of the processing time. It's a model that runs a loop calculating a bunch of data as if it's running every day. To complete one run (almost 3000 days worth of data) takes the model two days to finish so time is somewhat important.

**XOR LX** · 03-09-2014, 10:07 AM

Ah, I see. Interesting question. Let me get some advice on this.

Regards

**Tony Valko** · 03-09-2014, 10:53 AM

As long as the last number in the column is the end of the used range a sum of the entire column would be more efficient *if* the amount of data is always the same.

However, if one time you have 10k rows of data and the next time you only have 50 rows of data then the dynamic range formula would probably be better overall.

As soon as you have 10k rows of data that defines the used range even though at present you only have 50 rows of data.

**martindwilson** · 03-09-2014, 11:33 AM

fwiw for 10 recalculations range p1:p10000

	K	L	M
22		=SUM(P:P)	=SUM(P1:INDEX(P:P,MATCH(70^50,P:P)))
23
24	calculated time	0.00125	0.00134
25		0.00138	0.00169
26		0.00132	0.00128
27		0.00144	0.00168
28		0.00142	0.0014
29		0.00125	0.0014
30		0.00131	0.0015
31		0.00147	0.00137
32		0.00139	0.00181
33		0.00132	0.00147
34	average time to run	0.001355	0.001494

**XOR LX** · 03-09-2014, 12:33 PM

Nice and interesting analysis, Martin.

**Tony Valko** · 03-09-2014, 02:06 PM

There is calculation timer code here:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

Your mileage may vary depending on the configuration of your machine and versions of software used.

I use that code frequently and have buttons on my toolbars so I can quickly run tests.

**protonLeah** · 03-09-2014, 03:18 PM

Named Range: Rows2Sum ===>

Please Login or Register  to view this content.

Then

Please Login or Register  to view this content.

Summing a variable number of cells

LinkBack

Thread Tools

Rate This Thread

Display