Excel / Googlesheets formula query

Discussion in 'Programming' started by themickey, Dec 2, 2019.

  1. themickey

    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.
     
    MrKJoe likes this.
  2. Real Money

    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.
     
    MrKJoe and themickey like this.
  3. MichalTr

    MichalTr

    or you can just use 1 simple function

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

    excel attached
     
  4. themickey

    themickey

    Got it sussed hehehe.
    Use CONCAT function and INDIRECT function.
     
    tommcginnis and MrKJoe like this.
  5. themickey

    themickey

    Your reply is great, very simple, thanks, I'll use your method.
    Appreciated.
     
    MrKJoe and MichalTr like this.
  6. MichalTr

    MichalTr

    No problem. Happy to help.
     
    MrKJoe and themickey like this.
  7. avatar-ds

    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.