Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 12th 05, 04:41 PM
rlchavda
 
Posts: n/a
Default 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


  #2   Report Post  
Old July 12th 05, 05:27 PM
David Jessop
 
Posts: n/a
Default

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


  #3   Report Post  
Old July 12th 05, 05:31 PM
Max
 
Posts: n/a
Default

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),,,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" <[email protected] 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



  #4   Report Post  
Old July 12th 05, 06:13 PM
rlchavda
 
Posts: n/a
Default


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

Thanks
Raj.


--
rlchavdaPosted from http://www.pcreview.co.uk/ newsgroup access

  #5   Report Post  
Old July 12th 05, 06:20 PM
rlchavda
 
Posts: n/a
Default


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



  #6   Report Post  
Old July 12th 05, 06:49 PM
Max
 
Posts: n/a
Default

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" <[email protected] 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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 01:59 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017