How do I update monthly totals for the last 6 months?

[A Prince]

I am trying to figure out how to make a report look back for exactly 6 months and give me a total for that 6 months. I need it to do this every month, but only look at the previous 6 months.

For Example:

Jan Feb Mar Apr May Jun Jul Aug

Bob 6 5 0 3 5 1 0 4

Bill 3 1 5 3 7 2 4 1

Tom 4 0 0 3 3 1 0 4

So, in this example on the 1st of Jul bob should show 20 as the total of the previous 6 months, but on 1 Aug it should drop Jan, add July and show 14. This should work for all of the guys and run on into infinity as far as dates go. The 6 months might cover two different years. It sounds simple to me, but for the life of me I can't make it work..

Any help is greatly appreciated. BTW the numbers will probably never exceed 2 digits if that has any bearing.

 

[barry houdini]

Welcome to the Board

One way might be like this

Book1
	A	B	C	D	E	F	G	H	I	J
1		Jan-05	Feb-05	Mar-05	Apr-05	May-05	Jun-05	Jul-05	Aug-05	Sep-05
2
3	Bob	6	5	0	3	5	1	0	4	6
4
5	Bill	3	1	5	3	7	2	4	1	3
6
7	Tom	4	0	0	3	3	1	0	4	2
8
9
10		Last 6 months	if today is date in E10		01/08/2005
11	Bob	19	14
12	Bill	20	22
13	Tom	13	7
14
Sheet5

Dates in row 1 are 1st of each month formatted as mmm-yy

suggested formula is in B11

=SUM(OFFSET(INDEX($B$3:$B$7,MATCH($A11,$A$3:$A$7,0)),,MATCH(EDATE(TODAY(),-6),$A$1:Z$1)-2,,6))

copy down to B13

I also include an example to illustrate how the results would change if today wasn't 17th October but 1st August

 

[2rrs]

Using Barry's setup for Bob, Bill and Tom; this should give you the totals for the last 6 entries in Row 3, 5 and 7.

Bob =SUM(OFFSET(B3,0,COUNTA(3:3)-6,1,6))
Bill =SUM(OFFSET(B5,0,COUNTA(5:5)-6,1,6))
Tom =SUM(OFFSET(B7,0,COUNTA(7:7)-6,1,6))