SUM the Previous n Cell Values

Simonc64

Board Regular
Joined
Feb 15, 2007
Messages
178
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
 

Simonc64

Board Regular
Joined
Feb 15, 2007
Messages
178
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
Joined
May 28, 2005
Messages
41,722
Office Version
365
Platform
Windows
Try this, copied down.

Excel Workbook
AB
1NumbersSums
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
Joined
May 28, 2005
Messages
41,722
Office Version
365
Platform
Windows
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
1NumbersSums
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
Joined
Feb 15, 2007
Messages
178
Peter that is fantastic, thank you so much!! (Option 1)

Works like a dream!


Simon
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,722
Office Version
365
Platform
Windows
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
Joined
Apr 8, 2019
Messages
21
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
Joined
May 28, 2005
Messages
41,722
Office Version
365
Platform
Windows
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?
 

Forum statistics

Threads
1,077,823
Messages
5,336,569
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top