Auto-continuation of formula doesn't work

[mitjakleczka]

Dear all,

I am working on a formula that sums several values from 2 different excel sheets into one value, hence it looks somehow like this:
=SUM($B$1*Sheet1!A1+...+$B$20*Sheet1!A20) (simplified)

I don't have any problem with the formula itself, but with automatically continuing it. The formula (typed into C1, Sheet2) contains data from 20 stacked rows (say, A1 to A20). I want to copy it into the field below, this time containing data from the next 20 rows (say, A21 to A40). In the field below, it should be data from A41 to A60 and so on.
However, it seems that I can't just auto-continue my formula - when I do that, excel will use the data from A2-A21 and A3-23, respectively. I also can't solve the problem manually, since I have to copy the formula around 500 times (each time containing data from 20 different rows).

I hope that it is understandable what my problem is - didn't find an easier way to put it

Does anyone have an idea how I can modify my formula, so that I can auto-continue it?

Any help will be highly apreciated.
Best,
Mitja

 

[Joe4]

Welcome to the Board!

First, note that if you are adding up each part individually with a "+" sign, you don't need to use SUM.
SUM basically says "add all the cells contained within this range". If you are already adding them with a "+" sign, the SUM function serves no purpose.

Basically, you could replace a long addition calculation like this:
=A1 + A2 + A3 + ... + A10
with this:
=SUM(A1:A10)

That is how you use the SUM function.

So, what you really have is just this:
=$B$1*Sheet1!A1+...+$B$20*Sheet1!A20
You can replace that with this:
=SUMPRODUCT(--(Sheet1!A1:A20),--(Sheet2!B1:B20))

I think the easiest way to populate this formula in cells C1:C500 is to use VBA to build the formula I posted above and write it to each cell like this:

Sub MyFormulaPopulate()


    Dim myRow As Long
    Dim sRow As Long
    Dim eRow As Long
    
    Application.ScreenUpdating = False
    
    Sheets("Sheet2").Activate
    
'   Copy formula down 500 times
    For myRow = 1 To 500
'       Calculate start row
        sRow = (myRow - 1) * 20 + 1
'       Calculate ending row
        eRow = myRow * 20
'       Populate formula in column C
        Cells(myRow, "C").Formula = "=SUMPRODUCT(--(Sheet1!A" & sRow & ":A" & eRow & _
            "),--(Sheet2!B" & sRow & ":B" & eRow & "))"
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

If you copy that code into the VB Editor and run it, it should populate all your formulas.