CODEWITHSUNDEEP
excelexcel-formuladata-analysisautofill
Trevor wells
Trevor wells

Reputation: 1

Is there something I can add into my formula to autofill my cell range in increments of 12 instead 0f 1?

Formula autofill help

Hello, I'm learning excel and for work I need to combined large chunks of data from one sheet to another. I came up with the formula =SUM('940748 SB Class 05-08-23'!F1:F12) in the second sheet for the first batch of data from sheet one. The problem I am having is when fill down the formula I need the cell range to increase by increments of 12 not 1, so F1:F12 becomes F13,F24 when I fill down one row. My knowledge of excel is very limited so I have no clue on were to even begin to get this done. Here is the data from sheet one, each data set that needs to be combined highlighted in alternating colors. enter image description here Here is what I need the combined data to look like in sheet 2, the highlighted rows are the end result of the formula and match the data set from sheet 1.enter image description here So the data from sheet1 A1 - A12 are added together and the total is put in cell A1 of sheet2, and so on for B1 - B12. I want to be able to fill the formula down and sheet2 cell A2 will be the sum of A13 - A24. Sheet1 has 6948 rows of data and I cant spend all day on it.

Upvotes: 0

Views: 88

Answers (1)

Harun24hr
Harun24hr

Reputation: 36890

INDIRECT() as well as INDEX() will work. I suggest to use non volatile function INDEX(). Try-

=SUM(INDEX(Sheet2!A:A,ROW($A1)*12-11):INDEX(Sheet2!A:A,ROW($A1)*12))

For Microsoft-365 can try below formula if your data is continuous.

=BYROW(WRAPROWS(TOCOL(Sheet2!A:A,1),12,0),
LAMBDA(x,SUM(x)))

enter image description here

Upvotes: 1

Related Questions