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