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

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:

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


    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.


  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.


    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 –



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

      If the source data is on some other sheet such as Sheet1, this would calculate that:

  9. moahmed elabd says:

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

    • Tom Urtis says:

      …and when you presumably tried
      as I explained in my blog post, why did that not work for you?

  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:

    The data are in cells P2:B1441

    Here is the formula for increments of three:


    Works really well.

    And here is the formula for increments of twelve:


    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,
      what cell is the second formula in for every 12 increments?


  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

    Q3: OFFSET12 runs expected output for yrs 1895-1902:
    actual output from OFFSET12:

    formula used:

    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:

    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:

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


  17. Mandi says:


    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:

    I have tried using the formula; however, it only returns zeros.

    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.

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

        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.

  20. S says:

    So the formula is only working for me when i input it in the same sheet as the referenced cells. Is there a way to fix that.
    Im basically trying to put the sum of a group of cells, in a cell on a different sheet, if the sum of a different group of cells is equal to zero. Those groups of cells are in a different sheet (but same document) as the sheet on which im inputting this function.


    Thank you

  21. C says:

    Hey Tom,


    So Im using that formula to get the sum of a group of cells, if the sum of the main group of cells is 0. I used the offset command and it works, as long as i input it on the same sheet as the cells. My problem is i need to input that formula in a different sheet, and its just giving me zeroes.

    Thank You

  22. C says:

    Sheet 1 is my other sheet. My active sheet is the one in which i’m inputting the mentioned function.

  23. Patrick says:

    What about 7 x rows and 10 x columns? For weekly expenditure for example. Thanks.

    • Tom Urtis says:

      The number of columns is irrelevant to this example. Just reference the column your numbers are in.
      As for the formula, if it’s every 7 rows then as my example says, for column J (maybe the tenth column you refer to),

  24. Patrick says:

    And if I want to translate that exact formula and exact sum from the initial sheet to another sheet what do I do?

    • Tom Urtis says:

      Seems it should be like any other formula that refers to another sheet.
      Assuming your data is on Sheet1 and your formuls is on Sheet 2, then

  25. Cham says:

    Hi Tom, what if the incremental summing has to be done only for cells that are non-zero? How should the formula be tweaked? Thanks!

    • Tom Urtis says:

      Hi Cham – –

      Well, wow, just thinking about that, there are a couple issues to consider. For example, are negative numbers included. I assume empty cells (or cells containing null strings) are assumed to be zero and hence bypassed. So does this mean that, for example, with this scenario…

      [] ColA
      2 25
      6 36
      7 42
      8 11
      9 37
      10 14
      11 49
      12 35

      …would the 4 numbers in range A1:A8 be summed, and then the numbers in A9:A12 be summed? That would mean less about increments as far as rows are concerned and more about values as far as cell contents are concerned. If that is the case, if it were me I’d use a VBA macro, or better yet a Worksheet Change event to keep the sum column updated with each change. For instance, if cell A4 becomes populated (which in my above depiction is currently empty), that would shift the entire calculation ranges. I would not rely on a native formula solution for this scenario, but maybe you have some other worksheet design in mind that gave rise to your question.

      • Cham says:

        Thanks for responding, Tom. What I am trying to do is to add cells in increments of 12 starting from cell W3 onwards. The formula that you have shared worked {=SUM(OFFSET($W3,0,(COLUMN()-COLUMN(G3))*24, 1,24))}, but I need it tweak it so that the summing of increments of 12 will only start on the first non-zero cell. For example: Cells W3 to Z3 are zero, then the summing of increments of 12 will only start on AA3 onwards. My data does not have negative numbers. 🙂

  26. Cham says:

    Formula correction: =SUM(OFFSET($W3,0,(COLUMN()-COLUMN(G3))*12, 1,12))

    • Tom Urtis says:

      A few more questions then…

      • My example showed an operation for cells down a single column. It sounds like you want an operation for cells along a single row?
      • No negative numbers, but what if a zero (0) is in a “first cell with a number”, cell, does that qualify as the starting point?
      • Any empty (blank) cells?

  27. Cham says:

    Hi Tom,

    I have prepared a sample google spreadsheet so you can see the sample data:

    • My example showed an operation for cells down a single column. It sounds like you want an operation for cells along a single row?
    Answer: Yup, the formula is per row. The formula should add the values per row, starting on the cell with a value at an increments of 12. I need to add values for Y1 and Y2 (and so on)so the increment is per 12 set of values. For Y1, the goal is to sum 12 sets of values (starting on the cell with a value), then for Y2, the goal is to sum 24 sets of values starting on the cell with a value) and so on.

    • No negative numbers, but what if a zero (0) is in a “first cell with a number”, cell, does that qualify as the starting point?
    Answer: If the cell contains a zero value, the formula should skip it and start on the first cell that contains a value.

    • Any empty (blank) cells?
    Answer: The empty / blank cells are characterized by 0 values.

    Thanks in advance!

  28. Cham says:

    Hi Tom, I am really sorry for responding late.
    The expected result from the formula is to know the total cost per row year on year, given that the start of the 12 month year differs.

    • Tom Urtis says:

      If 21 is the correct result in cell A2 as you show it, and the cells will contain only zero or a number and zeros are to be ignored, why don’t you just sum from D2:O2? It’s the same for the other rows too; I don’t see what the complication is.

  29. Cham says:

    Hi Tom,

    Thanks for responding, and apologies for the confusion.

    Ideally, row 2 for Y1 should be 78 instead of 21.

    The formula should be able to pick up J2 as the starting point of the formula since this is the first non-zero value in the row. J2:U2 should be added for Y1 instead of D2:O2.

    For Y2, it’s the same. J2:AA2 should be added for Y2 instead of D2:AA2.

    The year-on-year summation of values should be in increments of 12 (Y1: First 12 values, starting on the first non-zero value; Y2: First 24 values, starting on the first non-zero value…and so on.)


    • Tom Urtis says:

      In cell A2 would be this formula:


      This is an ARRAY FORMULA.
      You apply it to a cell by pressing Ctrl + Shift + Enter NOT just with Enter.

      Then, copy cell A2 down as needed.

  30. Cham says:

    Thanks for all the help, Tom. (And apologies if this resulted to several back and forth responses)

    I used the formula that you have shared but I got the same total.

    I tried this workaround in the meantime: =IFERROR(ADDRESS(ROW(),MIN(IF($D2:AA20,COLUMN($D2:AA2))),2),””)
    This locates the first cell that is non-zero. This will result to J$2 in my sample spreadsheet.

    Then, I supplied the cell location from the first formula =SUM(OFFSET(J$2,0,(COLUMN()-COLUMN(A2))*12, 1,12))

    We can now mark this as closed.

    Thanks again!

    • Tom Urtis says:

      If you got the same total then your worksheet is set up differently than what you posted because I got the exact totals you said you expected. But if you somehow have it working the way you want, that is all that matters.

  31. Nojeem Jimoh says:

    Thanks for the post Mr. Tom.

    How can I sum consecutive nth column in a row? That is C6:G6, H6:L6, M6:Q6 and so on. Your post only focus on summing consecutive nth row in a column.

  32. Terenita says:

    Can I use this formula for a Logical OR as well? Or any other function that gives the result of n rows, like AVERAGE, MEDIAN, AND? Would I just replace SUM() with OR()?


    • Tom Urtis says:

      Not sure how you’d want to apply the concept. Are you saying you want to do something if, for example, every 5 rows do not sum to an expected result? An example of what you are trying to achieve would help clarify your question.

  33. Elaine says:

    Hi can I do an averageif function for this and omit 0’s in the cells?
    So far this is what I have and it works, but I want to remove all 0’s in the average: =AVERAGE(OFFSET($B$4,(ROW()-ROW($B$4))*7,0,7,1))

    • Tom Urtis says:

      Assuming the cells you say contain 0’s are not empty but really contain a zero, this should do what you ask for:

  34. Dave says:

    Hello, similar to your published example above but I need to output sum to be on non sequential rows. For example, the sum of Week 1 would be G2, sum of Week 2 would be G7, and so on.

    • Tom Urtis says:

      Depending on how your worksheet is organized, a formula solution might be (and probably is) possible, but not knowing how your data is set up, the VBA solution in this example can get you started with the concept of how to accomplish this.

Leave a Reply

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