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")

Improve this question
2
  • 1
    Use INDIRECT() based on ROW().
    – Akina
    Commented Oct 5, 2018 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.
    – Rajesh Sinha
    Commented Oct 8, 2018 at 11:46

1 Answer 1

Reset to default
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. ( :

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .