Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Conditional tests with Sumproduct
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Conditional tests with Sumproduct
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Conditional tests with Sumproduct
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Conditional tests with Sumproduct
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Conditional tests with Sumproduct
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Conditional tests with Sumproduct
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Conditional tests with Sumproduct
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
Get highest scores multiple tests & students | Excel Discussion (Misc queries) | |||
Using Multiple Tests and Sounds With Worksheet Macros | Excel Discussion (Misc queries) | |||
Countif Using Multiple Logic Tests | Excel Worksheet Functions | |||
Average a group of tests for grade, some tests not taken by all. | Excel Discussion (Misc queries) |