Excel / Googlesheets formula query

[themickey]

I have numerous columns of data where I wish to sum, lets say 99 rows.
=sum(A1:A99) =sum(B1:B99) =sum(C1:C99) =sum(F1:F99) =sum(E1:E99) etc etc

Frequently I wish to change the 99 rows to another number, for example 50 rows.

How can I change the row number just once rather than having to change the formula in every column?

This as I wish to change the row numbers to something different every day, having to change numerous column formulas becomes very tedious.

 

[Real Money]

You record a macro. Then you can run the macro with one keystroke.

Or you can name the cells. You can name a group of cells, and then put the name into the sum functions. Look up named ranges in excel tutorials.

 

[MichalTr]

or you can just use 1 simple function

=SUM(OFFSET(firstValueWith$$signs,0,0,cellWhereYouTypeNumberOfCells))

excel attached

 

[themickey]

Got it sussed hehehe.
Use CONCAT function and INDIRECT function.

 

[themickey]

or you can just use 1 simple function

=SUM(OFFSET(firstValueWith$$signs,0,0,cellWhereYouTypeNumberOfCells))

excel attached

Your reply is great, very simple, thanks, I'll use your method.
Appreciated.

 

[MichalTr]

Your reply is great, very simple, thanks, I'll use your method.
Appreciated.

No problem. Happy to help.

 

[avatar-ds]

For relatively small columns (a few thousand figures) I would frequently just use a quick and dirty inline fixed maximum length figure such as SUM(A:A 1:10000), problem solved. For an advanced approach requiring performance optimization and clarity I'd use named ranges or tables. While named ranges mostly just offer offer clarity by eliminating or simplifying complex reference grammar with OFFSET, INDEX etc., with tables Excel will auto-name and also auto-expand everything automatically.