[ryguy7272]

I am trying to do a lookup and sum values, simultaneously. I posted a
question about this the other day, but didnt get any useful responses. Im
sure the answer is out there somewhere, I just havent 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 wont 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 its confusing, thats why
I havent found a solution yet and thats why Im 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.

Im pretty sure it is going to be something with sumproduct; I just cant
figure it out€¦

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


Thanks,
Ryan---



--
RyGuy

[Max]

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.

Assuming the goal values are in col C in Goals
perhaps you meant to do something like this in Master:
=SUMPRODUCT(--(ISNUMBER(MATCH(Goals!B$2:B$100,{"Lee";"Jay"},0))) ,Goals!C$2:C$100)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"ryguy7272" wrote:
I am trying to do a lookup and sum values, simultaneously. I posted a
question about this the other day, but didnt get any useful responses. Im
sure the answer is out there somewhere, I just havent 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 wont 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 its confusing, thats why
I havent found a solution yet and thats why Im 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.

Im pretty sure it is going to be something with sumproduct; I just cant
figure it out€¦

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

[T. Valko]

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

[T. Valko]

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

[ryguy7272]

Thanks for the follow up Biff! I committed your function with C+S+E.
However, I must have referenced the wrong range of cells; all I got was a
bunch of zeros. Your solution would be much more elegant than mine, if I
could get it working.

As an alternative, I came up with this:
=IF(ISNA(INDEX(Goals!$C$2:$C$500,MATCH('Filtered
List'!C2,Goals!$B$2:$B$500,0))),0,INDEX(Goals!$C$2 :$C$500,MATCH('Filtered
List'!C2,Goals!$B$2:$B$500,0)))

I use this function in Column E. That gives me all goals, but there is one
problem now. With ID numbers in Column A, and Goals in Column E (based on
the index/match function above), I can use this in Column F, in row 45, and
get the correct result for my overall calculation:
=SUMPRODUCT(--($A$1:$A$50=A45),$E$1:$E$50)

However, because of a special situation (hard to explain), this only works
if there are no duplicates in Column C. If a name shows up once, like Lee or
Jay, the function works fine, but if Lee shows up twice in Column C, or if
Jay shows up twice in Column C, then the function double counts everything.
Is there a way to sum the values in Column E, based on the values in Column
A, but only do the sum for unique values in Column C? In other words, can I
modify my sumproduct function to only use unique values in Column C, and
ignore dupes?

Its asking for a lot, I know. If I can find get this last piece of the
puzzle, my model should work fine. If someone knows of a way to do this,
please share.

Regards,
Ryan---


--
RyGuy


"T. Valko" wrote:

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

[T. Valko]

If you want to send me a *small* sample file that shows me what you want I
can give it a shot. Do you still have my address?

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
Thanks for the follow up Biff! I committed your function with C+S+E.
However, I must have referenced the wrong range of cells; all I got was a
bunch of zeros. Your solution would be much more elegant than mine, if I
could get it working.

As an alternative, I came up with this:
=IF(ISNA(INDEX(Goals!$C$2:$C$500,MATCH('Filtered
List'!C2,Goals!$B$2:$B$500,0))),0,INDEX(Goals!$C$2 :$C$500,MATCH('Filtered
List'!C2,Goals!$B$2:$B$500,0)))

I use this function in Column E. That gives me all goals, but there is
one
problem now. With ID numbers in Column A, and Goals in Column E (based on
the index/match function above), I can use this in Column F, in row 45,
and
get the correct result for my overall calculation:
=SUMPRODUCT(--($A$1:$A$50=A45),$E$1:$E$50)

However, because of a special situation (hard to explain), this only works
if there are no duplicates in Column C. If a name shows up once, like Lee
or
Jay, the function works fine, but if Lee shows up twice in Column C, or if
Jay shows up twice in Column C, then the function double counts
everything.
Is there a way to sum the values in Column E, based on the values in
Column
A, but only do the sum for unique values in Column C? In other words, can
I
modify my sumproduct function to only use unique values in Column C, and
ignore dupes?

It's asking for a lot, I know. If I can find get this last piece of the
puzzle, my model should work fine. If someone knows of a way to do this,
please share.

Regards,
Ryan---


--
RyGuy


"T. Valko" wrote:

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

[ryguy7272]

I know you helped me a few times before! Thanks, again, for the prior
assists. As I remember, you gave me the dynamic-offset-filter:
=INDEX(B4:B620,MATCH(1,(SUBTOTAL(3,OFFSET(B4:B620, ROW(B4:B620)-MIN(ROW(B4:B620)),0,1)))*(B4:B620<""),0))

I still use it!

I do not have your email address, but here is mine:

Please send me an email and I will reply with the problematic file.

Thanks so much!!
Ryan--


--
RyGuy


"T. Valko" wrote:

If you want to send me a *small* sample file that shows me what you want I
can give it a shot. Do you still have my address?

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
Thanks for the follow up Biff! I committed your function with C+S+E.
However, I must have referenced the wrong range of cells; all I got was a
bunch of zeros. Your solution would be much more elegant than mine, if I
could get it working.

As an alternative, I came up with this:
=IF(ISNA(INDEX(Goals!$C$2:$C$500,MATCH('Filtered
List'!C2,Goals!$B$2:$B$500,0))),0,INDEX(Goals!$C$2 :$C$500,MATCH('Filtered
List'!C2,Goals!$B$2:$B$500,0)))

I use this function in Column E. That gives me all goals, but there is
one
problem now. With ID numbers in Column A, and Goals in Column E (based on
the index/match function above), I can use this in Column F, in row 45,
and
get the correct result for my overall calculation:
=SUMPRODUCT(--($A$1:$A$50=A45),$E$1:$E$50)

However, because of a special situation (hard to explain), this only works
if there are no duplicates in Column C. If a name shows up once, like Lee
or
Jay, the function works fine, but if Lee shows up twice in Column C, or if
Jay shows up twice in Column C, then the function double counts
everything.
Is there a way to sum the values in Column E, based on the values in
Column
A, but only do the sum for unique values in Column C? In other words, can
I
modify my sumproduct function to only use unique values in Column C, and
ignore dupes?

It's asking for a lot, I know. If I can find get this last piece of the
puzzle, my model should work fine. If someone knows of a way to do this,
please share.

Regards,
Ryan---


--
RyGuy


"T. Valko" wrote:

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