lookup & sum for 12 months

[JonesyUK]

Hi, please help:

I have a spreadsheet that has months along the top(P1, P2, P3, etc, and names along the side(name1, name2, name3 etc,)

I need a formula that will sum 12 months, starting at Px, based on the correct name:

Example1: Period = P2, Name = name3
Formula: vlookup name 3, and then sum from P2 to P13 (12 months) in that row

Example2: Period = P3, Name = name1 (in row2)
Formula: vlookup name1, and then sum P3 to P14 (12 months) in that row

Hope this makes sense,

Thanks a million....

C

 

[gsbelbin]

I've put P1, P2 ... P21 in A1 to V1.
I've put Name 1 to Name 16 in A2 to A17.
The cells B2 to V17 hold the values you want to add.
I've got A22 to hold the period (just a number) and B1 to hold the Name.

The following formula should give the result you want.

=SUM(INDIRECT("R" & MATCH(B22,$A$2:$A$17,0)+1 & "C" & MATCH("P" & A22,$B$1:$V$1,0)+1 & ":R" & MATCH(B22,$A$2:$A$17,0)+1 & "C" & MATCH("P" & A22,$B$1:$V$1,0)+12, FALSE))

Hope this helps.

 

[Peter_SSs]

If you don't want to use a lot of these formulas on your sheet, you could use this sort of idea.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Period	P2
2	Name	Name 3
3	Sum	56
4
5		P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14	P15	P16	P17	P18
6	Name 1	1	5	1	5	5	5	1	5	1	5	5	1	5	5	5	5	5	5
7	Name 2	2	6	2	6	4	4	2	6	2	6	4	2	6	4	6	4	2	0
8	Name 3	5	3	5	3	7	7	5	3	5	3	7	5	3	7	3	7	11	15
9	Name 4	1	1	1	1	8	8	1	1	1	1	8	1	1	8	1	8	15	22
10	Name 5	4	2	4	2	6	6	4	2	4	2	6	4	2	6	2	6	10	14
Sum 1

If you have a lot of them, or just want to avoid using the volatile OFFSET function, then you could use something like this.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Period	P2	2
2	Name	Name 3	3
3	Sum	56
4
5		P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14	P15	P16	P17	P18
6	Name 1	1	5	1	5	5	5	1	5	1	5	5	1	5	5	5	5	5	5
7	Name 2	2	6	2	6	4	4	2	6	2	6	4	2	6	4	6	4	2	0
8	Name 3	5	3	5	3	7	7	5	3	5	3	7	5	3	7	3	7	11	15
9	Name 4	1	1	1	1	8	8	1	1	1	1	8	1	1	8	1	8	15	22
10	Name 5	4	2	4	2	6	6	4	2	4	2	6	4	2	6	2	6	10	14
Sum 2