Home

Excel formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-416947%22%20slang%3D%22en-US%22%3EExcel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-416947%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys%2C%20robably%20very%20simple%20but%20i%20can%20do%20it...%20How%20do%20i%20copy%20a%20block%20of%207%20rows%20down%20in%207's%20on%20excel%3F%20Example%20would%20be%20copying%20%3Dsum(A1%3AA7)%20and%20making%20the%20copy%20come%20out%20as%20%3Dsum(A8%3AA12)%20-%20%3Dsum(A12%3AA18).....%20etc%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-416947%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-417117%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-417117%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317446%22%20target%3D%22_blank%22%3E%40watche87%3C%2FA%3E%26nbsp%3B%2C%20if%20your%20first%20formula%20is%20in%20first%20row%2C%20when%3C%2FP%3E%0A%3CPRE%3E%3DSUM(INDEX(A%3AA%2C1%2B(ROW()-ROW(%24A%241))*7)%3AINDEX(A%3AA%2C7%2B(ROW()-ROW(%24A%241))*7))%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20in%20down.%20Sample%20is%20in%20attached%20file.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-417495%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-417495%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20so%20much%20Sergei%20but%20i%20still%20cant%20make%20it%20work.%20So%20if%20i%20am%20putting%20the%20information%20onto%20a%20new%20sheet%20from%20a%20sheet%20called%26nbsp%3BDaily2018%20and%20block%20of%207%20rows%20i%20want%20to%20copy%20would%20be%20D4%3AD10%20what%20would%20the%20formula%20be%20then%3F%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-417914%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-417914%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317446%22%20target%3D%22_blank%22%3E%40watche87%3C%2FA%3E%26nbsp%3B%2C%20sorry%2C%20perhaps%20I%20misunderstood%20you.%20You'd%20like%20to%20copy%20information%20from%20one%20sheet%20to%20another%20by%20blocks%20of%207%20rows%3B%20or%20you%20would%20like%20to%20make%20the%20calculations%20on%20the%20blocks%20of%207%20rows%3F%20If%20you%20could%20provide%20small%20sample%20file%20that%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-417977%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-417977%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BSo%20i%20want%20to%20copy%20the%20weekly%20DRINK%20total%20figure%20over%20to%20my%20new%20sheet%20to%20create%20a%20week%20on%20week%20graph%20of%202018%20v%202019.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20example%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-418715%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-418715%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317446%22%20target%3D%22_blank%22%3E%40watche87%3C%2FA%3E%26nbsp%3B%2C%20with%20above%20formula%20that%20will%20be%3C%2FP%3E%0A%3CPRE%3E%3DSUM(INDEX(Daily2018!D%3AD%2C2%2B(ROW()-ROW(%24B%243))*7)%3AINDEX(Daily2018!D%3AD%2C8%2B(ROW()-ROW(%24B%243))*7))%3C%2FPRE%3E%0A%3CP%3EAs%20variant%20that%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIFNA(SUM(OFFSET(Daily2018!%24D%241%2CMATCH(A3%2CDaily2018!A%3AA%2C0)-1%2C%2C7))%2C0)%3C%2FPRE%3E%0A%3CP%3Eif%20only%20each%20your%20week%20has%20exactly%207%20days.%20It%20could%20be%20SUMIFS%20or%20SUMPRODUCT%20if%20reorganize%20your%20data%20a%20bit%20and%20have%20week%20number%20in%20each%20row.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-419470%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-419470%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bthat%20worked%20Sergei%2C%20thanks%20your%20a%20genius!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-419494%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-419494%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317446%22%20target%3D%22_blank%22%3E%40watche87%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
watche87
New Contributor

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

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

 

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?

@Sergei Baklan 

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

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

 

Please see example attached.

 

Thanks

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

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

@watche87 , you are welcome