How do I Lookup and SUM multiple "finds?"

[Guest]

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

 

[jan]

Russ,

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

will do, if your data are in A1 to F3

Jan

 

[Dave Peterson]

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)

 

[Guest]

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))

 

[Guest]

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

Cheers!

 

[Guest]

Thanks Dave. Many ways to skin the cat!

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)