Multiple Conditional tests with Sumproduct

[ryguy7272]

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

 

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

 

[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

 

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

 

[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?

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

 

[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

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

 

[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:
(e-mail address removed)
Please send me an email and I will reply with the problematic file.

Thanks so much!!
Ryan--