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))
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
So are you saying you want to sum, for example, B1:F1,G1:K1, and so on?
Yep, exactly.
Yes, I want to sum B4:G4 and place that total in B14, then sum H4:K4 and place that total in C14 and then carry on from there in the same increment.
What is the difference between the above formula and making a normal sum formula like
〓sum(D2:D6) and so on
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.
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.
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 —
Thanks for your scope-dependent idea. I’ll keep it in mind.
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
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.
how do i make formula for incremental series like
C1=A1
C2=A1+A2
C3= A1+A2+A3
So on
In cell C1 and copy down:
=SUM($A$1:A1)
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.
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))
hi i neet to sum diffrent cells each 10 cells in sequence cells
…and when you presumably tried
=SUM(OFFSET($D$2,(ROW()-ROW($D$2))*10,0,10,1))
as I explained in my blog post, why did that not work for you?
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?
Where does the sum of I7:I11 show up? In cell J7 or in cell J11?
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?
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.
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
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.
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!
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.
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.
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.
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
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!
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))
Hello
Help me please to sum one cell every 45 cells
E4 + E49 + E94 and so on.
E38 + E83 + E128 and so on
Array formula (confirm by pressing Ctrl+Shift+Enter, not just with Enter):
=SUM(IF(MOD(ROW(E4:E1000)-4,45)=0,E4:E1000))
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.
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/
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.
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.
What is a set? Maybe a column? Or maybe A1:A28 and so on? I’d probably employ a user defined function but it depends on how the ranges are established.
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.
=IF((SUM(OFFSET(Sheet1!$A$1,(ROW()-ROW(Sheet1!$A$1))*9,0,9,1)))>0,(SUM(OFFSET(Sheet1!$A$1,(ROW()-ROW(Sheet1!$A$1))*9,0,9,1))),(SUM(OFFSET(Sheet1!$B$1,(ROW()-ROW(Sheet1!$B$1))*9,0,9,1))))
Thank you
Hey Tom,
=IF((SUM(OFFSET(Sheet1!$A$1,(ROW()-ROW(Sheet1!$A$1))*9,0,9,1)))>0,(SUM(OFFSET(Sheet1!$A$1,(ROW()-ROW(Sheet1!$A$1))*9,0,9,1))),(SUM(OFFSET(Sheet1!$B$1,(ROW()-ROW(Sheet1!$B$1))*9,0,9,1))))
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
Is your other sheet Sheet1 or is Sheet1 your active sheet.
Sheet 1 is my other sheet. My active sheet is the one in which i’m inputting the mentioned function.
What about 7 x rows and 10 x columns? For weekly expenditure for example. Thanks.
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),
=SUM(OFFSET($J$2,(ROW()-ROW($J$2))*7,0,7,1))
But I’m on about an area of cells. Say, from cell B2 to N9, then B10 to N17 an so on. Thanks Tom.
Your further explanation suggests the need for a screen shot or more detail to know what column should be the start column to then increment from there. I recommend posting your question on this excellent forum,
https://www.mrexcel.com/forum/excel-questions/
which is free, and better suited for that kind of visual detail, and where many experts visit and can assist.
And if I want to translate that exact formula and exact sum from the initial sheet to another sheet what do I do?
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
=SUM(OFFSET(Sheet1!$J$2,(ROW()-ROW(Sheet1!$J$2))*7,0,7,1))
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!
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
1
2 25
3
4
5
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.
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. 🙂
Formula correction: =SUM(OFFSET($W3,0,(COLUMN()-COLUMN(G3))*12, 1,12))
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?
Hi Tom,
I have prepared a sample google spreadsheet so you can see the sample data:
https://docs.google.com/spreadsheets/d/1-XbUw56cEgC1ZhOCWNgvnR-rHLTtU0_8_G_z6BVrHhY/edit?usp=sharing
• 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!
I have a hunch about something but before I say what it is, please supply a few expected results from each row based on this data.
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.
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.
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.)
Thanks!
In cell A2 would be this formula:
=SUM(INDIRECT((ADDRESS(ROW(),COLUMN(INDIRECT(ADDRESS(ROW(),MATCH(TRUE,D2:AA2<>0,0)+3))))&":"&ADDRESS(ROW(),COLUMN(INDIRECT(ADDRESS(ROW(),MATCH(TRUE,D2:AA2<>0,0)+3)))+11))))
This is an ARRAY FORMULA.
You apply it to a cell by pressing
Ctrl + Shift + EnterNOT just withEnter.Then, copy cell A2 down as needed.
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!
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.
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.
Hello Nojeem – –
Assuming your numbers go from cell C6 to cell V6 and you want to sum each set of 5 cells as you said, then in cell W6 you can enter =SUM(OFFSET($C6,0,(COLUMN()-COLUMN($W$6))*5, 1,5)) and copy it across to cell Z6.
Alright. Thank you Mr. Tom
Sorry for replying late. It worked like magic. Once again, thank you so much Mr. Tom. I’m really grateful
You are quite welcome, Nojeem.
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()?
Thanks
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.
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))
Assuming the cells you say contain 0’s are not empty but really contain a zero, this should do what you ask for:
=AVERAGEIF((OFFSET($AB$4,(ROW()-ROW($AB$4))*7,0,7,1)),”<>0″)
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.
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.