# SUM the Previous n Cell Values

#### Simonc64

##### Board Regular
Hi Guys

Been struggling with this on and off for a few days and finally given in! Seems so simple but cant get my head round the logic!

Column A contains numerical values (for examples sake lets say the column runs from A2:A36) A1 is a text heading

In Column B I need a formula that i can run thru cells B2:B36 that SUMS every 6th value (B1 is a text heading)

I have tried the OFFSET formula as follows =SUM(OFFSET(A7,(ROW()-8)*6,0,6,1)),"") which works fine for rows B2:B6, but of course when the formula reached B12 it falls over because the offset references are absolute and dont change.

Tearing my hair out so ANY help welcomed!

Thanks

Simon

#### mikerickson

##### MrExcel MVP
Would =A2+A8+A14+A20+A26+A32 do what you want?

Last edited:

#### Simonc64

##### Board Regular
Would =A2+A8+A14+A20+A26+A32 do what you want?
Hi Mike thanks for the reply but no that doesnt help, let me be a bit more specific.

If the actual sum of A2:A7 =10. i need B7 to equal 10; then if the actual sum of A8:A13 =15. i need B13 to equal 15

#### Peter_SSs

##### MrExcel MVP, Moderator
Try this, copied down.

Excel Workbook
AB
22
36
46
58
68
7232
86
99
109
111
129
13337
144
151
164
172
187
19220
203
216
226
231
244
25323
264
271
289
298
301
31427
328
339
344
353
367
 Sum every 6

#### Peter_SSs

##### MrExcel MVP, Moderator
Alternatively, if you know the first sum is in A7 & you are happy to start there, you could try this simpler formula.

Excel Workbook
AB
22
36
46
58
68
72
86
99
109
111
129
13337
144
151
164
172
187
19220
203
216
226
231
244
25323
264
271
289
298
301
31427
328
339
344
353
367
 Sum every 6 (2)

#### Simonc64

##### Board Regular
Peter that is fantastic, thank you so much!! (Option 1)

Works like a dream!

Simon

#### Peter_SSs

##### MrExcel MVP, Moderator
Peter that is fantastic, thank you so much!! (Option 1)

Works like a dream!

Simon
You're welcome. Thanks for letting us know.

#### JACOBB9900

##### New Member
Hello,

Ireplied to this thread because I am looking for a similar solution. I havenumbers in rows 6 and 7. Row 6 has set numbers that will not change. I need tosum the numbers in row 6 starting from cell K6 to E6 and place a “1” in K7 once the sum of the cells, starting from K6is equal to or less than 8. For example. If K6+J6+I6=7.8 and K6+J6+I6+H6=8.5then the 1 would need to go in cell I7. Then I need to start summing up thecells again starting from H6 until a total of 8 or less is reached. At thispoint I would need to a 2 instead of a 1.
Explanation: Each cell in row 6 has a number whichrepresents hours. Row 7 needs to show how many days are accumulated. In thiscase we are going by 8-hour work days so every time the sum of the cells in row6 get close to equaling 8 without going over 8 I need to add a day in row 7.
I’m sorry, I can not upload an example. This is a workcomputer and DropBox is blocked from sharing. If my explanation is a little confusing,I’m more than happy to answer any detailed questions. Finding the solution willdefinitely save me tons of time. Every bit of help is appreciated.

Thank you.

#### Peter_SSs

##### MrExcel MVP, Moderator
I think I'd need to see a set or 2 of sample data with the expected results.

At worst, in the Reply window, click Go Advanced & then in the tools at the top left of the window is an option to insert a table. Make sure you select Full Grid in Table Style and also allow enough rows and columns to include the row/column labels or else make sure you tell us clearly what range you have shown.

BTW, what web browser are you using?

#### JACOBB9900

##### New Member
Thank you I will respond with an example soon. I am using Internet Explorer.