Drag formula 1 cell to the right but have the range of the sum move 3

Hi all,

I have data in E4 to DA4. In E11 I have the formula =sum(E4:G4). When I drag the formula to the one cell to the right I want the range if the sum to skip to H4:J4. How do I do this? Thank you so much in advance.

Kind regards,

Martin

You can use this formula in E11:

=SUM(INDEX($E$4:$Z$4,(COLUMNS($E:E)-1)*3+1):INDEX($E$4:$Z$4,COLUMNS($E:E)*3))

then copy across as required. You may need to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings.

Hope this helps.

Pete

Or even the formula you have already been given

Formula:

Please Login or Register  to view this content.

Hi Pete,

You're a hero.

I had to modify it a bit but it work like a charm: =SUM(INDEX($E$4:$DB$4,(COLUMNS($E:E)-1)*3+1):INDEX($E$4:$DB$4,COLUMNS($E:E)*3)).

Could you maybe explain why this works the way it does?

Thank you so much again.

Yeah I couldnt get this work for some reason and didnt remember where i post the question hence the report. My bad.

Originally Posted by MWDB

... Could you maybe explain why this works the way it does? ....

The COLUMNS function returns the number of columns in the range. Initially the range is $E:E (note that the first is anchored to column E), so it returns 1, but when it is copied across the range changes to $E:F (returning 2), then to $E:G (returning 3), and so on. In the first INDEX the number returned by the COLUMNS function has 1 subtracted and it then multiplied by 3, and then has +1 added back in, so this gives rise to a series of numbers 1, 4, 7, etc. In the second INDEX the number returned by the COLUMNS function is just multiplied by 3, thus giving rise to the numbers 3, 6, 9, and so on.

The INDEX function returns a cell or range reference, so in this instance of the formula that reference would be E4:G4, and in the next cell it will be H4:J4, then K4:M4 and so on, and the SUM function then adds those 3 cells together.

Hope this helps.

Pete



P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete