extending a sum formula

Paul_crt

Hello,

I have to update a lot of sheets so that they will be ready for 2020, and i need to write a lot of sums formula, because if i expand them normally, the first one is SUM([revenue]13:[revenue]16) then the second one becomes SUM([revenue]14:[revenue]17) then SUM([revenue]15:[revenue]18) etc. But i dont want the same cells to be used everytime, id like for it to go SUM([revenue]17:[revenue]20) SUM([revenue]21:[revenue]24) etc.

The perfect thing for me would be to be able to select a number of cell and copy all the formulas at once and them paste that to the next sheet,

or else if there is another way to fill the formula that would provide a different outcome ?

 

Thanks,

Paul

 

Paul Newcome

Are you able to provide some screenshots with sensitive/confidential data hidden, removed, or replaced with "dummy data"? If you could include some that have manually entered data to reflect your desired outcome, that would really help.

Paul_crt

well i guess its empty for now so i'll provide screenshots

 this is the first formula, it is correct

Paul_crt

if i extend it to january :

Paul_crt

i'd actually need for january to be 117 120. but dont bother with that anyway, i'm already almost done doing it manuallly, and even then it wouldnt be perfect. is there a way to copy the formula inside a selection of cells and paste it into another selection of cells that has the same number of cells in another sheet ? that would still be a lifesaver

Paul Newcome

Which column in the screenshot is the column you are trying to sum within?

 

Unfortunately a copy/paste of a block of cells will only paste the data and not the formulas.

 

You would need to go into each cell individually as if you are going to edit it, select all, copy, then paste.

Paul Newcome

What is the reason for needing to copy/paste the formulas? It could be (depending on your setup) that saving the sheet as new and just updating a few cells would work.

Paul_crt

no because there are multiple different links per sheet. only this part is common between different sheets

Paul Newcome

Ah. Ok.

 

Which column in the screenshot are you trying to sum?