Incrementing a formula by more than a value of 1 when dragging down a row

[jeffdlinn]

I have an Amortization table in a sheet that has 360 rows and one of the columns shows the interest payment each month. In a separate sheet I have 30 rows which represent 30 years.

I have the following formula in one cell to sum the first 12 months of interest: =sum(Amortization!D6:D17) as expected when I drag this formula down the formula changes as expected to

=sum(Amortization!D6:D17)
=sum(Amortization!D7:D18)
=sum(Amortization!D8:D19)
=sum(Amortization!D9:D20)
, etc.
When I drag the formula instead of incrementing by one I'd like the formula to increment by 12 so it captures the next 12 months. As an example, it would look like this:
=sum(Amortization!D6:D17)
=sum(Amortization!D18:D29)
=sum(Amortization!D30:D41)
=sum(Amortization!D42:D53)
, etc.

 

[bebo021999]

Try:

=SUMPRODUCT((ROW($D$1:$D$1000)>=(ROWS($6:6)-1)*12+6)*(ROW($D$1:$D$1000)<=(ROWS($6:6)-1)*12+17)*Amortization!$D$1:$D$1000)

 

[Leo Skywalker]

You can try:

SUM(OFFSET(AMORTIZATION!$D$6,12*(ROW(A1)-1,0,12,1))

 

[jeffdlinn]

You can try:

SUM(OFFSET(AMORTIZATION!$D$6,12*(ROW(A1)-1,0,12,1))

MRExcel

Financial Model PURCHASE,REFINANCE,CASH FLOW,MONTHLY,YEARLY BRRRRFECT,$67,230,After Repair Value,$290,000,Gross Rent,$2,900,$34,800 Purchase Price,$95,000,ARLTV,75.00%,Vacancy,-,$145,$1,740 Rehab Costs,+,$150,270,Operating Income,=,$2,755,$33,060 Amount Financed,-,$198,669,Refinance Loan Amount...

docs.google.com

I gave this formula a try but received a parsing error. If you look at the Yearly Tab (cell K12) of the sheet linked above you see it returns an error.

 

[Leo Skywalker]

I see your Spreadsheet in Spanish, use the translation:

SUMA(DESREF(AMORTIZATION!$D$6;12*(FILA(A1)-1;0;12;1))

 

[jeffdlinn]

I see your Spreadsheet in Spanish, use the translation:

SUMA(DESREF(AMORTIZATION!$D$6;12*(FILA(A1)-1;0;12;1))

That's odd the google sheet should be in English. I'm in the US

 

[jeffdlinn]

Try:

=SUMPRODUCT((ROW($D$1:$D$1000)>=(ROWS($6:6)-1)*12+6)*(ROW($D$1:$D$1000)<=(ROWS($6:6)-1)*12+17)*Amortization!$D$1:$D$1000)

MRExcel

Financial Model PURCHASE,REFINANCE,CASH FLOW,MONTHLY,YEARLY BRRRRFECT,$67,230,After Repair Value,$290,000,Gross Rent,$2,900,$34,800 Purchase Price,$95,000,ARLTV,75.00%,Vacancy,-,$145,$1,740 Rehab Costs,+,$150,270,Operating Income,=,$2,755,$33,060 Amount Financed,-,$198,669,Refinance Loan Amount...

docs.google.com

I tried this formula but received an error. Please look at the sheet labeled "Yearly" and look at cell K12 - This is where I entered your formula. In column M are the results that should show in Column K

 

[BigBeachBananas]

It's missing a parenthesis after -1

=SUM(OFFSET(AMORTIZATION!$D$6,12*(ROW(A1)-1),0,12,1))

 

[jeffdlinn]

It's missing a parenthesis after -1

=SUM(OFFSET(AMORTIZATION!$D$6,12*(ROW(A1)-1),0,12,1))

It's missing a parenthesis after -1

=SUM(OFFSET(AMORTIZATION!$D$6,12*(ROW(A1)-1),0,12,1))

Thanks @BigBeachBananas that did the trick

 

[Leo Skywalker]

My bad, I typed the formula by hand, all uppercase, and it seems a misplaced comma.