How do I Lookup values from another sheet, knowing that there will be
multiple occurances of those values, and sum the offset cells?

For example:

Lookup value is:
BAKER

Table is:
SMITH BAKER JONES BROWN BAKER HODGES
4 5 6 3 8 10
7 5 9 56 3 1


I want a forumla that uses the Lookup "Baker" and SUMS the second row of 5+3
to yield 8? Can I do this w/out using VBA? (He asked hopefully...)

Thanks for any insight.

Russ

Russ,

=SUMPRODUCT(--(A1:F1="BAKER"),A2:F2)

will do, if your data are in A1 to F3

Jan

As a formula in your worksheet, you could use:

=sumif($a$1:$x$1,"baker",$a2:$x2)
or
=sumif(sheet2!$a$1:$x$1,"baker",sheet2!$a2:$x2)



xrbbaker wrote:
>
> How do I Lookup values from another sheet, knowing that there will be
> multiple occurances of those values, and sum the offset cells?
>
> For example:
>
> Lookup value is:
> BAKER
>
> Table is:
> SMITH BAKER JONES BROWN BAKER HODGES
> 4 5 6 3 8 10
> 7 5 9 56 3 1
>
> I want a forumla that uses the Lookup "Baker" and SUMS the second row of 5+3
> to yield 8? Can I do this w/out using VBA? (He asked hopefully...)
>
> Thanks for any insight.
>
> Russ

--

Dave Peterson

Never mind! I figured it out with a SUM(IF array) formula. I was getting
hung up trying to get HLOOKUP to do the trick.

=SUM(IF('[RM Data Entry_ Release.xls]Management Summary'!$D$2:$AG$2=C$1,'[RM
Data Entry_ Release.xls]Management Summary'!$D43:$AG43,0))

"xrbbaker" wrote:

> How do I Lookup values from another sheet, knowing that there will be
> multiple occurances of those values, and sum the offset cells?
>
> For example:
>
> Lookup value is:
> BAKER
>
> Table is:
> SMITH BAKER JONES BROWN BAKER HODGES
> 4 5 6 3 8 10
> 7 5 9 56 3 1
>
>
> I want a forumla that uses the Lookup "Baker" and SUMS the second row of 5+3
> to yield 8? Can I do this w/out using VBA? (He asked hopefully...)
>
> Thanks for any insight.
>
> Russ
>

Cool Jan. thanks I just figured out another way, but very similar to yours.

Cheers!

"jan" wrote:

> Russ,
>
> =SUMPRODUCT(--(A1:F1="BAKER"),A2:F2)
>
> will do, if your data are in A1 to F3
>
> Jan
>
>
>

Thanks Dave. Many ways to skin the cat!

"Dave Peterson" wrote:

> As a formula in your worksheet, you could use:
>
> =sumif($a$1:$x$1,"baker",$a2:$x2)
> or
> =sumif(sheet2!$a$1:$x$1,"baker",sheet2!$a2:$x2)
>
>
>
> xrbbaker wrote:
> >
> > How do I Lookup values from another sheet, knowing that there will be
> > multiple occurances of those values, and sum the offset cells?
> >
> > For example:
> >
> > Lookup value is:
> > BAKER
> >
> > Table is:
> > SMITH BAKER JONES BROWN BAKER HODGES
> > 4 5 6 3 8 10
> > 7 5 9 56 3 1
> >
> > I want a forumla that uses the Lookup "Baker" and SUMS the second row of 5+3
> > to yield 8? Can I do this w/out using VBA? (He asked hopefully...)
> >
> > Thanks for any insight.
> >
> > Russ
>
> --
>
> Dave Peterson
>