Lookup and sum possible
Hey Guys, Can some one please help me with the following problem. suppose my 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 I type T1 30 (basically if i type T1 then it find where T1 is and sum that row from 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. -- rlchavdaPosted from http://www.pcreview.co.uk/ newsgroup access |
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 "rlchavda" wrote: Hey Guys, Can some one please help me with the following problem. suppose my 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 I type T1 30 (basically if i type T1 then it find where T1 is and sum that row from 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. -- rlchavdaPosted from http://www.pcreview.co.uk/ newsgroup access |
Assume the source table is in A1:D5
Assume you put in A7: T1, you could put in say, B7: =SUMPRODUCT(OFFSET($B$1,MATCH($A7,$A$2:$A$5,0),,,2 55)) Copy B7 down as required to return likewise for other inputs in A8, A9 ... Adapt to suit .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "rlchavda" <rlchavda.1s2df3@ wrote in message ... Hey Guys, Can some one please help me with the following problem. suppose my 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 I type T1 30 (basically if i type T1 then it find where T1 is and sum that row from 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. -- rlchavdaPosted from http://www.pcreview.co.uk/ newsgroup access |
David, Thanks , u really helped me. So fast and so perfect with explaination. Thanks Raj. -- rlchavdaPosted from http://www.pcreview.co.uk/ newsgroup access |
Hi, I have got one answer like SUM(OFFSET(B1,MATCH(A10,A2:A5,FALSE),0,1,55)) and yours also seems to be what I was looking for.Thanks I greatly appreciate both of your help. Thanks Raj. -- rlchavdaPosted from http://www.pcreview.co.uk/ newsgroup access |
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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "rlchavda" <rlchavda.1s2hq4@ wrote in message ... Hi, I have got one answer like SUM(OFFSET(B1,MATCH(A10,A2:A5,FALSE),0,1,55)) and yours also seems to be what I was looking for.Thanks I greatly appreciate both of your help. Thanks Raj. -- rlchavdaPosted from http://www.pcreview.co.uk/ newsgroup access |
All times are GMT +1. The time now is 10:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com