Lookup and sum possible

[rlchavda]

Hey Guys,
Can some one please help me with the following problem. suppose m
execel looks like as below

Activity Day1 Day2 Day3
T1 10 10 10
T2 20 20 20
T3 30 30 30
T4 40 40 40

Now I need to do the following. In one of the cell below this if
type

T1 30
(basically if i type T1 then it find where T1 is and sum that row fro
col2 to end)

if I type

T2 60

If I type
T3 then it should show 90.

Hope some one will have idea on how to do this ?
Thanks
Raj

 

[David Jessop]

Hi,

I'll breakdown the solution into bits (although you could, if you felt
really masochistic do it in one go)

Let's assume your first column is column A and that you type "T1" in cell
A10. Then make cell B10 be

=MATCH(A10,A2:A5,FALSE)

if you've put T2 in A10 then this should return 2.

Then put

=SUM(OFFSET(B1,B10,0,1,3))

in C10. This will give you what you need. You could make it a bit more
flexible, but hopefully this is a start.

HTH,

David

 

[Max]

Assume the source table is in A15

Assume you put in A7: T1, you could put in say, B7:
=SUMPRODUCT(OFFSET($B$1,MATCH($A7,$A$2:$A$5,0),,,255))

Copy B7 down as required to return likewise for other inputs in A8, A9 ...

Adapt to suit ..

 

[rlchavda]

David,
Thanks , u really helped me. So fast and so perfect wit
explaination.

Thanks
Raj

 

[rlchavda]

Hi,
I have got one answer lik
SUM(OFFSET(B1,MATCH(A10,A2:A5,FALSE),0,1,55)) and yours also seems t
be what I was looking for.Thanks

I greatly appreciate both of your help.

Thanks
Raj

 

[Max]

You're welcome !

I used "255" as the width param in the OFFSET
to cover all the cols from col B across to col IV