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

  • 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 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

    Can you please help at all?
    Many thanks

  • Re: Sum 5 rows - question


    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.

    [COLOR="darkred"]Please note the change to your title, which is based on the objective stated in your thread.[/COLOR]

    If the new title still does not accurately describe your thread you may make further edits as needed per the above

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!