0

Disclaimer: I think similar questions have been asked before, but the answers were all quite complicated, involving special cases or index functions etc.

Is it possible to autofill a formula were the references are not just plus one? Consider this example: I want the column filled by these formulas:

=Sum(A1:A3)
=Sum(A4:A6)
=Sum(A7:A9)
etc.

Is there a simple way of defining a formula to tell Excel to increment the number by three. Something like: =Sum(A1 "+3":A3 "+3")

2
  • 1
    Use INDIRECT() based on ROW().
    – Akina
    Oct 5 '18 at 12:11
  • To Auto fill the Formula you need VBA code like this,, Range("C2").Formula = "=Sum(A1:A3)" . Just try this in Immediate Window. Oct 8 '18 at 11:46
2

Since 1,4,7.. and 3,6,9.. is on its own a repeatable numbers.. my (favorite) solution is to use 2 extra column to generate the series.. and one to combine them.

using Column C & D as extra column, put :

C1  ---> 1
C2  ---> 4
C3  ---> 7
D1  ---> 3
D2  ---> 6
D3  ---> 9

and in E1 put :

E1  ---> =SUM(INDIRECT("A"&C1,TRUE):INDIRECT("A"&D1,TRUE))

then drag all 3 columns downwards..

Hope it helps. ( :

2
  • Nicely composed Answer,, perfect 10 ☺ Oct 8 '18 at 11:31
  • the trick : =SUBSTITUTE(CELL("address",C2),"$","")&" ----> "&FORMULATEXT(C2) ( : hope it'll be useful.
    – p._phidot_
    Oct 10 '18 at 3:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.