How to maintain a set range of cells when dragging a formula (e.gmonthly>annual data)

  • Thread starter Justinalexander
  • Start date
J

Justinalexander

If I have a formula such as SUM(A1:A12) and drag/copy it one cell down
it becomes SUM(A2:A13), but what if I only want to use each cell once,
say to convert monthly data to annual data. so that dragging the
formula down would give:

SUM(A13:A24) then
SUM(A25:A36)
SUM(A37:A48) etc.
 
P

Pete_UK

Put this in your first cell:

=SUM(INDIRECT("A"&ROW(A1)*12+1&":A"&(ROW(A1)+1)*12))

then copy down as required.

Hope this helps.

Pete
 
K

Khoshravan

If your new data for new month, is beneath the present month, then dragging
the sum for present will populate the data for next month.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top