Forum Discussion

watche87's avatar
watche87
Copper Contributor
Apr 09, 2019

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)...
excel
Formulas and Functions
watche87's avatar
watche87
Copper Contributor
Apr 09, 2019

SergeiBaklan 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.

 

Please see example attached.

 

Thanks

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 09, 2019

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.

  • watche87's avatar
    watche87
    Copper Contributor
    Apr 09, 2019

    SergeiBaklan that worked Sergei, thanks your a genius!!

Resources