SUM the Previous n Cell Values

[Simonc64]

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]

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

 

[Simonc64]

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]

Try this, copied down.

Excel Workbook
	A	B
1	Numbers	Sums
2	2
3	6
4	6
5	8
6	8
7	2	32
8	6
9	9
10	9
11	1
12	9
13	3	37
14	4
15	1
16	4
17	2
18	7
19	2	20
20	3
21	6
22	6
23	1
24	4
25	3	23
26	4
27	1
28	9
29	8
30	1
31	4	27
32	8
33	9
34	4
35	3
36	7
Sum every 6

 

[Peter_SSs]

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

Excel Workbook
	A	B
1	Numbers	Sums
2	2
3	6
4	6
5	8
6	8
7	2	32
8	6
9	9
10	9
11	1
12	9
13	3	37
14	4
15	1
16	4
17	2
18	7
19	2	20
20	3
21	6
22	6
23	1
24	4
25	3	23
26	4
27	1
28	9
29	8
30	1
31	4	27
32	8
33	9
34	4
35	3
36	7
Sum every 6 (2)

 

[Simonc64]

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

Works like a dream!


Simon

 

[Peter_SSs]

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

Works like a dream!


Simon

You're welcome. Thanks for letting us know.

 

[JACOBB9900]

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]

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]

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