VLOOKUP? Sums of multiple columns

[groovyjames]

this is probably really simple compared with the other problems I've seen...

i've got an excel file with descriptions in the first column and values spread out in multiple columns to the right:

A 10 20 30
B 5 12 39
C 98 32 12

i need a function which will search for a value in column A and return the sum of the values in columns B to D, ie for "a" the answer would be 60

i've tried using VLOOKUP(A:A;"A";B:B)+VLOOKUP(A:A;"A";C:C)+VLOOKUP(A:A;"A";C:C) but the formular is too long for excel when I increase the number of columns. i thought VLOOKUP(A:A;"A";B:D) could work, but it doesn't. does anyone know how I could solve this problem?

cheers, james

 

[P Sitaram]

As a first thought, why not have a total column and pick up that value through VLOOKUP ?

 

[groovyjames]

thanks for the speedy reply.

the problem is that the number of columns which I want to add is not constant. the columns represent months - sometimes I want to calculate jan to mar for example, and sometimes jan to dec.

 

[P Sitaram]

Look at the sheet below:

book1.xls
	A	B	C	D	E	F	G	H
1	Names	Month1	Month2	Month3	Month4	Month5	Month6	Month7
2	Name1	1	2	3	4	5	6	7
3	Name2	11	12	13	14	15	16	17
4	Name3	21	22	23	24	25	26	27
5	Name4	31	32	33	34	35	36	37
6	Name5	41	42	43	44	45	46	47
7	Name6	51	52	53	54	55	56	57
8	Name7	61	62	63	64	65	66	67
9	Name8	71	72	73	74	75	76	77
10	Name9	81	82	83	84	85	86	87
11	Name10	91	92	93	94	95	96	97
12
13	Name	Months	Sum
14	Name7	4	250
Sheet3

The formula in C14 is:

=SUM(OFFSET(INDEX($A$2:$A$11,MATCH(A14,$A$2:$A$11,0)),,1,1,B14))

 

[rs2k]

Not a complete solution, but if you highlight the deired cells and look at the bottom right of your screen, it will sum the highlighted cells for you. I don't know of a way to link that to a cell though, sorry.

Colin.

 

[groovyjames]

great! thanks psitaram - exactly what I wanted. don't understand the formular one bit, but it gives me the numbers which i want.

thanks to rs2k as well

james