# Sum blocks of cells comprised of X rows

• Hi I need to sum 5 rows at a time, i.e. a1:a5 then B6:B10 etc - which I can do BUT how do I get this information to go in cell C2, C3, C4 etc

So cell C2 will contain the sum of B2:B6
C3 will contain sum of B7:B11
C4 will contain sum of B12:B16 and so on etc

I know I can do sum (B2:B6) and then highlight cells C2:C6 and drag down
But that gets me the answer every 5th line, and I need it on every line

I know i could also manually write this in, but i need to do this for hundreds of times so this isnt possible

Any help you could provide in a formula would be greatly appreciated

Kind regards
Erin

## Files

• Re: Sum 5 rows - question

=SUM(OFFSET(\$B\$1,ROW()*5-9,0,5))

copy down.

• Re: Sum 5 rows - question

Hi, Erin
Nice to meet you.
First, write =A2 anywhere blank columns and drag down. say, cell address 'e12'.
in cell F12=IF(A2="",B2+F11,B2), drag down.
Second move F12:F39 upward so that locate 'value 78 in cell F16' on the left of '1st April'.
You can do filter non zero for column E and copy&Paste.
Regards, junho

• Re: Sum 5 rows - question

forum.ozgrid.com/index.php?attachment/39070/Thank you that worked perfectly for that example i sent.
What would i modify if i wanted to have my answers on another tab/sheet and also starting on a different row number.

i.e. i have attached a worksheet (example 2) which is very similar to the actual i am trying to work from.

Production! is the tab with the information, i want to start gathering the data from R5:R10 then R11:R15 etc
The tab i want the information to collect on is Bottles! and i want to start collecting the data on L5, L6, L7 etc
So Bottles! L5 has the sum of Production!R5:R10 and Bottles! L6 has the sum of Production! R11:R15 etc

I tried but couldnt get it to completely work
I managed to start pulling data from the other sheet by adding this into your equation =SUM(OFFSET(Production!\$R\$1,ROW()*5-9,0,5))
But i cant get the right cells to add up correctly

i.e. L5 should = 3008 / L6 = 4008 / L7 = 0 / L8 = 489 etc

Many thanks
Erin

## Files

• Re: Sum 5 rows - question

=SUM(OFFSET(Production!\$R\$5,(ROW()-4)*6,0,6))

in l5, copy down.

Explanation - What offset does is pick a cell and move down "rows" number of cells and grab an area "height" number of rows from here. so in this case we always start from R5. we move down a number of cells = the current row-4 (so 1 for l5, 2 for l6 etc), * 6 (so 6 for l5, 12 for l5 etc), and get an area 6 cells in height to sum.

• Re: Sum blocks of cells comprised of X rows

Hello nire000,

Welcome to Ozgrid. We're glad to have you on board, however, please note the following regarding thread titles:

Thread titles are used in searching the forum, therefore, it is vital they be written to accurately describe your [COLOR="blue"]thread content or overall objective[/COLOR] using ONLY search friendly key words. That is, your title use as search terms would return relevant results.

• The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
• The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters (e.g. slashes, commas, colons, etc)
• The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different

Your title of "[COLOR="red"]Sum 5 rows - question[/COLOR]" does not describe your thread or objective and is not helpful to those searching the forum for a solution to a similar need.