SUM the Previous n Cell Values

Simonc64

Active Member
Joined
Feb 15, 2007
Messages
251
Office Version
  1. 365
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
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
 
Upvote 0
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
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 (2)
 
Upvote 0
Peter that is fantastic, thank you so much!! (Option 1)

Works like a dream!


Simon
 
Upvote 0
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.

 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top