 • 541K Members
• 6,195 Online
• 644K Conversations

Highlighted

# Excel formula help

Hi Guys, robably very simple but i can do it... How do i copy a block of 7 rows down in 7's on excel? Example would be copying =sum(A1:A7) and making the copy come out as =sum(A8:A12) - =sum(A12:A18)..... etc

7 Replies
Highlighted

# Re: Excel formula help

@watche87 , if your first formula is in first row, when

`=SUM(INDEX(A:A,1+(ROW()-ROW(\$A\$1))*7):INDEX(A:A,7+(ROW()-ROW(\$A\$1))*7))`

and drag in down. Sample is in attached file.

Highlighted

# Re: Excel formula help

Thanks so much Sergei but i still cant make it work. So if i am putting the information onto a new sheet from a sheet called Daily2018 and block of 7 rows i want to copy would be D4:D10 what would the formula be then?

Highlighted

# Re: Excel formula help

@watche87 , sorry, perhaps I misunderstood you. You'd like to copy information from one sheet to another by blocks of 7 rows; or you would like to make the calculations on the blocks of 7 rows? If you could provide small sample file that helps.

Highlighted

# Re: Excel formula help

@Sergei Baklan So i want to copy the weekly DRINK total figure over to my new sheet to create a week on week graph of 2018 v 2019.

Thanks

Highlighted

# Re: Excel formula help

@watche87 , with above formula that will be

`=SUM(INDEX(Daily2018!D:D,2+(ROW()-ROW(\$B\$3))*7):INDEX(Daily2018!D:D,8+(ROW()-ROW(\$B\$3))*7))`

As variant that could be

`=IFNA(SUM(OFFSET(Daily2018!\$D\$1,MATCH(A3,Daily2018!A:A,0)-1,,7)),0)`

if only each your week has exactly 7 days. It could be SUMIFS or SUMPRODUCT if reorganize your data a bit and have week number in each row.

Highlighted

# Re: Excel formula help

@Sergei Baklan that worked Sergei, thanks your a genius!!

Highlighted

# Re: Excel formula help

@watche87 , you are welcome

Related Conversations
Preparing a stacked chart
EuroSree in Excel on
0 Replies
Katharina_Stemmer1989 in Excel on
0 Replies
Linking external source (eg Share Price) to a cell
Aitch1964 in Excel on
0 Replies
Insert an image in Excel header
mat74 in Excel on
1 Replies
DAX Powerpivot - How to solve this problem in PowerPivot
kayyeah11 in Excel on
0 Replies
IF statements and conditional formatting
clare1981 in Excel on
1 Replies