I have a table, and I want to do a Vlookup on Column1, and when I find what I 
am looking for, I want to sum column 2 from that row down to the bottom of 
the table.

For example,
Item 1     2345
Item 2     3478
Item 3     1298
Item 4     123
Item 5     1233

I want to find "Item 3" and then I want to be able to return the sum of 
(1298+123+1233).

How would I do this? My thoughts were that I would do a lookup to get the 
row numner of "Item 3" and then do a sum() with the cell range starting from 
the row where I found "Item 3" but I'm not sure how to do that.

Thanks,
Steve

Hi,

I may have overdone this, i'm sure there's a simpler solution but in the 
meantime try this

=SUM(INDIRECT("B" & MATCH("item 3",A:A) &":B"& MATCH(LOOKUP(10^99,B:B),B:B)))

Mike
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Steve Haack" wrote:

> I have a table, and I want to do a Vlookup on Column1, and when I find what I 
> am looking for, I want to sum column 2 from that row down to the bottom of 
> the table.
> 
> For example,
> Item 1     2345
> Item 2     3478
> Item 3     1298
> Item 4     123
> Item 5     1233
> 
> I want to find "Item 3" and then I want to be able to return the sum of 
> (1298+123+1233).
> 
> How would I do this? My thoughts were that I would do a lookup to get the 
> row numner of "Item 3" and then do a sum() with the cell range starting from 
> the row where I found "Item 3" but I'm not sure how to do that.
> 
> Thanks,
> Steve

I put you data in A1:B5
In G1 I type: Item3
In H1 I used formula: =ADDRESS(MATCH(G1,A:A,0),2) which returned $B$3
In I1 I used formula: =SUM(INDIRECT(H1&":B100")) which retuned 2654 as 
required

It would be easy to combine H1 and I1
What if your data began in row 3, for example? Formula in H1 returns $B$5 
and all is well
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Steve Haack" <SteveHaack@discussions.microsoft.com> wrote in message 
news:45162502-E85B-48C6-8072-7D8E073DD906@microsoft.com...
> I have a table, and I want to do a Vlookup on Column1, and when I find 
> what I
> am looking for, I want to sum column 2 from that row down to the bottom of
> the table.
>
> For example,
> Item 1     2345
> Item 2     3478
> Item 3     1298
> Item 4     123
> Item 5     1233
>
> I want to find "Item 3" and then I want to be able to return the sum of
> (1298+123+1233).
>
> How would I do this? My thoughts were that I would do a lookup to get the
> row numner of "Item 3" and then do a sum() with the cell range starting 
> from
> the row where I found "Item 3" but I'm not sure how to do that.
>
> Thanks,
> Steve 

Try this...

Data in the range A2:B6

D2 = lookup value = Item 3

=SUM(B6:INDEX(B2:B6,MATCH(D2,A2:A6,0)))

-- 
Biff
Microsoft Excel MVP


"Steve Haack" <SteveHaack@discussions.microsoft.com> wrote in message 
news:45162502-E85B-48C6-8072-7D8E073DD906@microsoft.com...
>I have a table, and I want to do a Vlookup on Column1, and when I find what 
>I
> am looking for, I want to sum column 2 from that row down to the bottom of
> the table.
>
> For example,
> Item 1     2345
> Item 2     3478
> Item 3     1298
> Item 4     123
> Item 5     1233
>
> I want to find "Item 3" and then I want to be able to return the sum of
> (1298+123+1233).
>
> How would I do this? My thoughts were that I would do a lookup to get the
> row numner of "Item 3" and then do a sum() with the cell range starting 
> from
> the row where I found "Item 3" but I'm not sure how to do that.
>
> Thanks,
> Steve