Tom’s Tutorials For Excel: Incremental Summing: Every 5 Cells

Tom’s Tutorials For Excel: Incremental Summing: Every 5 Cells

Here’s how you can incrementally sum a range of cells. In the pictured example, each set of five cells in range D2:D26 are summed in range G2:G6.

The formula in cell G2 and copied down to cell G6 is
=SUM(OFFSET($D$2,(ROW()-ROW($D$2))*5,0,5,1))

You can do this with any incremental count; it need not be 5 cells. For example, if you want to sum each set of 9 cells, your formula would be modified like this:
=SUM(OFFSET($D$2,(ROW()-ROW($D$2))*9,0,9,1))

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
37 comments on “Tom’s Tutorials For Excel: Incremental Summing: Every 5 Cells
  1. mike says:

    Tom,

    I’m trying to do something similar but it’s adding 5 columns x 1 row (the opposite of the above).

    I have data for 5 days of the week and I want to sum it each week in a separate column.

    It really is basically the same thing as above but spred out for 52 weeks adding every 5 columns.

    I hope you can help. I am having a mind cramp on this one.

    TIA

  2. fahmy says:

    What is the difference between the above formula and making a normal sum formula like
    〓sum(D2:D6) and so on

    • Tom Urtis says:

      Thanks for following my Excel blog page.

      What formulas would the “and so on” possibly be? If you have the formula =SUM(D2:D6) in cell G2, then when you copy it to cell G3 you have the ineffective formula of =SUM(D3:D7) when in actuality you’d want the essence of =SUM(D7:D11). That is why the OFFSET and ROW functions come into play with my example. Now, if you only have a couple dozen rows such as I posted a picture of, yes, you can enter 5 different formulas, say in cells G2, G3, G4, G5, and G6. But I posted that picture with only 25 records (rows of data) so it can fit on the web page as a visual example. In real world business circumstances, that list of sales can be tens of thousands of rows deep. You would need a single formula, such as I posted in this example, that can simply be copied down to the next cells below it as I did for column G.

  3. Alison says:

    If you had lots and lots of rows, is there an easy way to move to the next five rows easier than changing the, D2 to D7 then D12 etc.

  4. drewbbc says:

    Tom — IMV – best would be to append the dollar values to a date table (insert a date table from my apps) – then uplift the DM and generate a pivot – ie scalable and sliceable across multiple dimension —

  5. Mitesh says:

    The best way is to use ‘SUMIF’ formula for these types of query.

    SUMIF(range,criteria,[sum_range])

    Range – A1:D26
    Critieria – A2 (This will change according to week and copy pasted accordingly)
    Sum_range – D2:D26

    If you have further queries on my suggestion, please drop me an email – mitesh170586@gmail.com

    Regards

    Mitesh

  6. Rohit says:

    Can I apply this formula in months as well, but in this case no. of days in a month will be different, then how it will work. Please suggest.

  7. sunay says:

    how do i make formula for incremental series like
    C1=A1
    C2=A1+A2
    C3= A1+A2+A3
    So on

  8. Sajid says:

    Dear Tom,

    Please help me on it.

    Total of A2:D2 at Sheet2’s A1
    Total of E2:H2 at Sheet2’s B1
    Total of I2:L2 at Sheet2’s C1
    ….and so on…

    Well, you tutorial-ed about ROW incremental but I need to learn same calculation but Column based.

    • Tom Urtis says:

      This would do that, assuming your source data being summed is also on Sheet2.
      =SUM(OFFSET($A1,COLUMN(),0):OFFSET($A1,COLUMN(),4))

      If the source data is on some other sheet such as Sheet1, this would calculate that:
      =SUM(OFFSET(Sheet1!$A1,COLUMN(),0):OFFSET(Sheet1!$A1,COLUMN(),4))

  9. moahmed elabd says:

    hi i neet to sum diffrent cells each 10 cells in sequence cells

  10. Michelle says:

    Hi Tom,

    I am trying to sum every 5 daily totals to a weekly total column. For example: i need I2:I6 sum in J6. and to keep this pattern going throughout the spreadsheet. Can you help?

  11. Lysanna says:

    Hi Tom,
    I am having a similar problem to Moahmed… I applied the formula with grand success summing in three cell increments, but in the same spreadsheet I am trying to sum in 12 cell increments and I am getting spurious answers that I can’t recreate by summing <12 cells. Though I have not tried all iterations. Is there an upper limit to the functionality perhaps?

    • Tom Urtis says:

      I’m not aware of an upper limit but it might have to do with your range of cells, such as the first actual row number of data. If you can provide the range’s location (please say what column the numbers are in as well), we can take it from there.

  12. Lysanna says:

    Ok
    The data are in cells P2:B1441

    Here is the formula for increments of three:

    =SUM(OFFSET($B$4,(ROW()-ROW($B$4))*3,0,3,1))

    Works really well.

    And here is the formula for increments of twelve:

    =SUM(OFFSET($B$2,(ROW()-ROW($B$2))*12,0,12,1))

    The differences in the row number for the start cell are real. The formula for increments of three starts on the 4th row in order to sum seasonal values of three months each with the first triad starting on the 3rd month (4th row). The second formula starts on row 2 (first cell with a value) because I am summing the monthly data to get annual values from the same data set.

    Thanks for you attention to this!

    some of the data:

    year.month ppt Seasonal (3months)
    1895.01 25.82 summed months decimal season codes
    1895.02 5.13 12 month offset formula 12 month sums year
    1895.03 6.84 12.45 MAM 1895.375 48.04 56.85 1895
    1895.04 2.56 0.14 JJA 1895.625 32.08 66.14 1896
    1895.05 3.05 5.39 SON 1895.875 60.70 48.04 1897
    1895.06 0.05 29.50 DJF 1896.125 42.99 32.08 1898
    1895.07 0.09 18.40 MAM 1896.375 46.07 60.7 1899
    1895.08 0 0.39 JJA 1896.625 70.79
    1895.09 1.88 12.90 SON 1896.875 48.71
    1895.1 0.27 30.19 DJF 1897.125 79.82
    1895.11 3.24 12.92 MAM 1897.375 39.43
    1895.12 7.92 1.20 JJA 1897.625 59.35
    1896.01 19.66 9.65 SON 1897.875 63.57
    1896.02 1.92 20.57 DJF 1898.125 34.01
    1896.03 5.42 6.27 MAM 1898.375 81.77
    1896.04 8.71 0.59 JJA 1898.625 31.41
    1896.05 4.27 8.16 SON 1898.875 43.80
    1896.06 0 20.95 DJF 1899.125 50.84
    1896.07 0.04 14.00 MAM 1899.375 52.82
    1896.08 0.35 0.28 JJA 1899.625 59.34
    1896.09 0.47 18.73 SON 1899.875 70.25
    1896.1 1.97 22.25 DJF 1900.125 50.91
    1896.11 10.46 11.38 MAM 1900.375 34.29
    1896.12 12.87 0.43 JJA 1900.625 35.65
    1897.01 4.98 12.40 SON 1900.875 46.93
    1897.02 12.34 27.47 DJF 1901.125 58.93
    1897.03 11.24 8.71 MAM 1901.375 43.90
    1897.04 1.17 0.00 JJA 1901.625 45.91
    1897.05 0.51 11.77 SON 1901.875 25.38
    1897.06 1.2 31.26 DJF 1902.125 40.34
    1897.07 0 16.74 MAM 1902.375
    1897.08 0 0.01 JJA 1902.625
    1897.09 0.46 18.28 SON 1902.875
    1897.1 2.93 25.18 DJF 1903.125
    1897.11 6.26 8.40 MAM 1903.375
    1897.12 6.95 0.06 JJA 1903.625
    1898.01 3.1 18.73 SON 1903.875
    1898.02 10.52 35.56 DJF 1904.125
    1898.03 1.51 25.57 MAM 1904.375
    1898.04 1.4 0.48 JJA 1904.625
    1898.05 3.36 14.37 SON 1904.875
    1898.06 0.59 24.25 DJF 1905.125
    1898.07 0 15.45 MAM 1905.375
    1898.08 0 0.00 JJA 1905.625
    1898.09 1.35 4.56 SON 1905.875
    1898.1 2.02 28.79 DJF 1906.125
    1898.11 4.79 18.31 MAM 1906.375
    1898.12 3.44 1.85 JJA 1906.625
    1899.01 13.46 3.03 SON 1906.875
    1899.02 4.05 32.30 DJF 1907.125
    1899.03 9.53 23.32 MAM 1907.375
    1899.04 1.93 1.82 JJA 1907.625
    1899.05 2.54 1.96 SON 1907.875
    1899.06 0.25 31.26 DJF 1908.125
    1899.07 0 5.50 MAM 1908.375
    1899.08 0.03 0.23 JJA 1908.625
    1899.09 0.53 8.12 SON 1908.875
    1899.1 5.58 59.57 DJF 1909.125
    1899.11 12.62 5.50 MAM 1909.375
    1899.12 10.18 0.13 JJA 1909.625

    • Tom Urtis says:

      Just to be clear, I rarely see ranges expressed in reverse order, so this you wrote:
      The data are in cells P2:B1441
      also means
      The data are in cells B2:P1441
      Correct or incorrect?

      Please tell me your expected results. I know that sounds odd to ask in this case but I want to make sure I know what you need. For example, the formula =SUM(OFFSET($B$4,(ROW()-ROW($B$4))*3,0,3,1)) that you said works so well will return what numbers exactly for, say, the first 3 or 4 calculations, based on the data you supplied.

      Also, what is your expected results for the 12 cell sums, for 3 sets of those? And what results are you getting that are incorrect?

      Finally, what cell is the first formula in for every 3 increments,
      and
      what cell is the second formula in for every 12 increments?

      Thanks.

  13. Lysanna says:

    Hey Tom,
    Q1: there is an error… I’m not sure how I managed *this* typo! but it should be B2:B1441; Not P… B. My apologies.

    So that could help a lot.

    Q2: the first three expected results in the OFFSET3 run are
    12.45
    0.14
    5.39
    29.50

    Q3: OFFSET12 runs expected output for yrs 1895-1902:
    56.85
    66.14
    48.04
    32.08
    60.7
    42.99
    46.07
    70.79
    actual output from OFFSET12:
    48.04
    32.08
    60.70
    42.99
    46.07
    70.79
    48.71
    79.82

    formula used:
    =SUM(OFFSET($B$2,(ROW()-ROW($B$2))*12,0,12,1))

    OBSERVATION: the expected OFFSET12 output is displaced by three cells. In other words their is a consistent lag of three cells in the output. Clearly this is operator error… but for the life of me I can’t see it.

    Q4: The first cell in the OFFSET3 run is B4.
    I am a bit unclear on the second part of this question: you ask which cell the formula is in (the output cell in my mind) but I think you are asking what cell the first input datum is in. So here are both answers

    the first formula in the OFFSET3 run is in cell D4,
    the first datum is in cell B4.
    the second formula in the OFFSET12 run is in cell H5,
    and the first input datum for the second formula is in cell B14.

    Formula used:
    =SUM(OFFSET($B$4,(ROW()-ROW($B$4))*3,0,3,1))

    I can send you an abbreviated .xlsx file if that would help.

    Thanks again for your attention to this. I am grateful for the assistance!

    • Tom Urtis says:

      The reason why you are getting the results you are getting for the 12 month compilation is that the ROW function is in the formula for a reason, and that is to reference the row in which the formula resides. Your formula for the 3-cell compilation works because your data of interest for that starts on row 4 and the formula to do the sums starts in row 4.

      In the case of the 12-cell compilation, your formula also begins in row 4 but the data of interest starts in row 2. Therefore, you have 2 choices:

      Choice #1
      Keep the formula exactly as you have it (exactly means exactly, like this: =SUM(OFFSET($B$2,(ROW()-ROW($B$2))*12,0,12,1)) BUT make it start in row 2 and copy down from there.

      Choice #2
      Keep the formula exactly WHERE you have it (starting in row 4) but modify it like so to allow for that 2-row difference by subtracting 2 from the row number of where the formulas reside:
      =SUM(OFFSET($B$2,(ROW()-2-ROW($B$2))*12,0,12,1))

      Either way, you end up with the same correct result.

  14. Lysanna says:

    Wow, great! It works!

    So, the simple rule here is to have the formula start in the same row as the data. easy peezy, yes?

    Thanks again Tom.

    • Tom Urtis says:

      The formula can start anywhere, you just need to tell excel in the formula what the starting row number is. The subtraction of 2 in this case with the original formula, if the formula was to stay in row 4, is because row 4 minus 2 equals row 2 where the data starts that is being compiled. If the original formula was put into cell A9 and copied down, for data starting in cell A2, then the formula would need to subtract 7 row numbers to refer to the starting point (row 9 minus 7 equals row 2). Therefore, the formula would be
      =SUM(OFFSET($B$2,(ROW()-7-ROW($B$2))*12,0,12,1))
      Looks like this one is completed. Thanks for visiting my website.

  15. Henry says:

    Looking to sum b7:i7 in b45, j7:q7 in c45 and so on, so every 8 cells across added together. Having trouble converting the formula above which is for adding numbers going down rather than across. Please could you help, thanks

  16. Victoria says:

    Hi there – is there an easy, sequential way to sum days in a fiscal month.

    For example, I have a full data sheet with orders by day for the entire year. I have successfully summed these orders by 52 weeks in a year. However, I am struggling to use this same data set with a 4-4-5 or 4-5-4 calendar. Meaning, some months are only 28 days (4 weeks) while others are 35 days (5 weeks). Is there an easy way to adjust the formula to account for this type of calendar?

    January – 28 Days =SUM(OFFSET(Days!$E$3,(ROW()-3)*28,0,28,1))
    February – 28 Days =SUM(OFFSET(Days!$E$3,(ROW()-3)*28,0,28,1))
    March – 35 Days =SUM(OFFSET(Days!$E$3,(ROW()-3)*35,0,35,1))

    When I replace the 28 with 35, I get a sum that’s off by about 255.

    Is it possible to adjust this to accommodate my needs?

    I appreciate the help!

    • Victoria says:

      Nevermind – I figured it out! I added the # of days in the month to the formula.

      =SUM(OFFSET(Days!E$3,0,0,$B$3,1))
      =SUM(OFFSET(Days!E$3,$B$3,0,$B$4,1))
      =SUM(OFFSET(Days!E$3,SUM($B$3:$B4),0,$B5,1))

  17. Mandi says:

    Hello

    Help me please to sum one cell every 45 cells
    E4 + E49 + E94 and so on.

    E38 + E83 + E128 and so on

  18. Chris says:

    Hi,
    I have tried using the formula; however, it only returns zeros.
    =SUM(OFFSET($AP$21467,(ROW()-ROW($AP$21467))*3600,0,3600,1))

    I would like to start from a specific row and sum for every 3600.

    • Tom Urtis says:

      Take a look at my blog post here that might help. Just plug in the range and increment you are working with. Note, it’s an array formula, so be sure to enter it with Ctrl+Shift+Enter, not just with Enter.
      https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-summing-every-nth-cell/

      • Chris says:

        I was trying to start from a specific row and sum 3600 numbers together. I saw a previous user was having the same issue- that is, to either start from the same row or subtract the numbers of rows from where I started.

        Choice #1
        Keep the formula exactly as you have it (exactly means exactly, like this: =SUM(OFFSET($B$2,(ROW()-ROW($B$2))*12,0,12,1)) BUT make it start in row 2 and copy down from there.

        Choice #2
        Keep the formula exactly WHERE you have it (starting in row 4) but modify it like so to allow for that 2-row difference by subtracting 2 from the row number of where the formulas reside:
        =SUM(OFFSET($B$2,(ROW()-2-ROW($B$2))*12,0,12,1))

        Either way, you end up with the same correct result.

        I used this way and was able to get the results. Thank you.

  19. JJ says:

    Hi Tom,

    How about for variable increments? So for example, for the first set i want to sum up until the 28th row, but on the second one, it’s 27, third is 28 and fourth is also 28 and fifth is 29.

Leave a Reply

Your email address will not be published. Required fields are marked *

*