How do I Lookup and SUM multiple "finds?"

X

xrbbaker

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
 
D

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

xrbbaker

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

xrbbaker

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

Cheers!
 
X

xrbbaker

Thanks Dave. Many ways to skin the cat!

Dave Peterson said:
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)
Click to expand...
 
Top