Vlookup

[kaushik]

Hi,
I'm a newbie to excel,

I need to lookup for a string for ex "Brew" or "Lunch" in column 3 and find the value of the cell in the same row and column 4. Add all these values and the sum should be displayed in say g3.

Thanks for all your help

[Ardus Petus]

=SUMPRODUCT(--(C1:C999="Brew"),D1:D999)

HTH
--
AP

"kaushik" <[email protected]> a écrit
dans le message de news:
[email protected]...
>
> Hi,
> I'm a newbie to excel,
>
> I need to lookup for a string for ex "Brew" or "Lunch" in column 3 and
> find the value of the cell in the same row and column 4. Add all these
> values and the sum should be displayed in say g3.
>
> Thanks for all your help
>
>
> --
> kaushik
> ------------------------------------------------------------------------
> kaushik's Profile:
> http://www.excelforum.com/member.php...o&userid=34715
> View this thread: http://www.excelforum.com/showthread...hreadid=544812
>

[kaushik]

Hi HTH,

Thanks for your quick response.

Extrapulating my earlier question. How do I do the same if the columns are in a different sheet. How do I reference to a different sheet.

Thanks a lot for your help.
-Kaushik

[Tom Ogilvy]

If that formula would produce the correct answer, then
Faster would be

=sumif(C:C,"Brew",D:D)

--
Regards,
Tom Ogilvy


"Ardus Petus" wrote:

> =SUMPRODUCT(--(C1:C999="Brew"),D1:D999)
>
> HTH
> --
> AP
>
> "kaushik" <[email protected]> a écrit
> dans le message de news:
> [email protected]...
> >
> > Hi,
> > I'm a newbie to excel,
> >
> > I need to lookup for a string for ex "Brew" or "Lunch" in column 3 and
> > find the value of the cell in the same row and column 4. Add all these
> > values and the sum should be displayed in say g3.
> >
> > Thanks for all your help
> >
> >
> > --
> > kaushik
> > ------------------------------------------------------------------------
> > kaushik's Profile:
> > http://www.excelforum.com/member.php...o&userid=34715
> > View this thread: http://www.excelforum.com/showthread...hreadid=544812
> >
>
>
>

[Ardus Petus]

Nice shot, Tom!

Since I discovered SUMPRODUCT, I do everything with it

Cheers,
--
AP

"Tom Ogilvy" <[email protected]> a écrit dans le message
de news: [email protected]...
> If that formula would produce the correct answer, then
> Faster would be
>
> =sumif(C:C,"Brew",D:D)
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Ardus Petus" wrote:
>
>> =SUMPRODUCT(--(C1:C999="Brew"),D1:D999)
>>
>> HTH
>> --
>> AP
>>
>> "kaushik" <[email protected]> a écrit
>> dans le message de news:
>> [email protected]...
>> >
>> > Hi,
>> > I'm a newbie to excel,
>> >
>> > I need to lookup for a string for ex "Brew" or "Lunch" in column 3 and
>> > find the value of the cell in the same row and column 4. Add all these
>> > values and the sum should be displayed in say g3.
>> >
>> > Thanks for all your help
>> >
>> >
>> > --
>> > kaushik
>> > ------------------------------------------------------------------------
>> > kaushik's Profile:
>> > http://www.excelforum.com/member.php...o&userid=34715
>> > View this thread:
>> > http://www.excelforum.com/showthread...hreadid=544812
>> >
>>
>>
>>

[Ardus Petus]

(With Tom's nice suggestion):
=SUMIF(Sheet2!C:C,"Brew",Sheet2!D:D)

Cheers,
--
AP

"kaushik" <[email protected]> a écrit
dans le message de news:
[email protected]...
>
> Hi HTH,
>
> Thanks for your quick response.
>
> Extrapulating my earlier question. How do I do the same if the columns
> are in a different sheet. How do I reference to a different sheet.
>
> Thanks a lot for your help.
> -Kaushik
>
>
> --
> kaushik
> ------------------------------------------------------------------------
> kaushik's Profile:
> http://www.excelforum.com/member.php...o&userid=34715
> View this thread: http://www.excelforum.com/showthread...hreadid=544812
>

[Tom Ogilvy]

I agree it is very powerful, but because of the way array formulas perform
(and it performs like an array formula) they can be very slow for large
ranges (I am sure you know that - put just repeating here for completeness).
So for single conditions, I try to use sumif or countif if possible and
leave the heavy lifting for sumproduct or an array formula when required.

--
Regards,
Tom Ogilvy


"Ardus Petus" wrote:

> Nice shot, Tom!
>
> Since I discovered SUMPRODUCT, I do everything with it
>
> Cheers,
> --
> AP
>
> "Tom Ogilvy" <[email protected]> a écrit dans le message
> de news: [email protected]...
> > If that formula would produce the correct answer, then
> > Faster would be
> >
> > =sumif(C:C,"Brew",D:D)
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Ardus Petus" wrote:
> >
> >> =SUMPRODUCT(--(C1:C999="Brew"),D1:D999)
> >>
> >> HTH
> >> --
> >> AP
> >>
> >> "kaushik" <[email protected]> a écrit
> >> dans le message de news:
> >> [email protected]...
> >> >
> >> > Hi,
> >> > I'm a newbie to excel,
> >> >
> >> > I need to lookup for a string for ex "Brew" or "Lunch" in column 3 and
> >> > find the value of the cell in the same row and column 4. Add all these
> >> > values and the sum should be displayed in say g3.
> >> >
> >> > Thanks for all your help
> >> >
> >> >
> >> > --
> >> > kaushik
> >> > ------------------------------------------------------------------------
> >> > kaushik's Profile:
> >> > http://www.excelforum.com/member.php...o&userid=34715
> >> > View this thread:
> >> > http://www.excelforum.com/showthread...hreadid=544812
> >> >
> >>
> >>
> >>
>
>
>