It's not as complicated as I thought...

A1:A9 = employee IDs
B1:B9 = supervisors
F1:F10 = another list of supervisors
G1:G10 = values to sum

Array entered** :

=SUM(IF(ISNUMBER(MATCH(F1:F10,IF(A1:A9=101,B1:B9), 0)),G1:G10))

As long as F1:F10 doesn't contain a boolean FALSE.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Unless the data is sorted or grouped by employee IDs then you'll need to
take an intermediate step of extracting all the supers that map to the
employee IDs. For example, this should be relatively easy:

101...Joe
101...Lisa
101...Sue
102...x
102...y

This won't be so easy:

101...Joe
102...y
101...Sue
102...x
101...Lisa


--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
I am trying to do a lookup and sum values, simultaneously. I posted a
question about this the other day, but didn't get any useful responses.
I'm
sure the answer is out there somewhere, I just haven't found it yet.

I have a sheet named 'Master' and this contains employee IDs in Column A
and
the employee name in Column B, and finally the supervisor name in column
C.
So, I want to lookup the number in column A and find the corresponding
name
in column C. As there will be a few identical numbers in column A, a
simple
vlookup or index/match won't work. Once I get the name of the supervisor
on
column C, I want to take these and match them to the names in column B of
a
sheet named Goals. Finally, I want to find the sum of all the values
(which
are goals) that correspond to these names. I know it's confusing, that's
why
I haven't found a solution yet and that's why I'm posting this question
again.

In short, I have the name 'Opie' in column B of sheet named 'Master'.
Opie
is mapped to 'Lee' and 'Jay', both in column C. I want to take these
names,
'Lee' and 'Jay' and compare them to names in column B of the 'Goals'
sheet
and then sum the goals for 'Lee' and 'Jay'.

I'm pretty sure it is going to be something with sumproduct; I just can't
figure it out.

If anyone can give me a solution, I would be most appreciative.


Thanks,
Ryan---



--
RyGuy