Thread: 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),,,255))

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 <at>yahoo<dot>com
----
"rlchavda" <rlchavda.1s2df3@> wrote in message
news:trCdnbGHWKD8d07fRVn_vg@giganews.com...
>
> 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 <at>yahoo<dot>com
----
"rlchavda" <rlchavda.1s2hq4@> wrote in message
news:5LKdnUmsX4CfnUnfRVn_vg@giganews.com...
>
> 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
>