Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a count from multiple lookups
On sheet1 I have a column with multiple names. I need a function that can
look up all those names from column B of sheet2, and then count the occurance of a value in column H of sheet2. Here's the example: Sheet1 (note that some rows are blank, and some names may appear 1 time): Helen Back Cyndi Lou Who Cyndi Lou Who Sam Spade Sheet2 (names only appear once): col B col H Cyndi Lou Who R2 Dan Danger R1 Dou Luva R3 Hal Brook R2 Helen Back R1 Joe Mama R2 Lou Duva R2 Mai Tai R2 Roberta Flack R3 Ron Popeil R2 Sam Spade R2 Will Not R2 Yoda n/a So I need a formula that can do lookup sheet1's names in col B of sheet2, then give me a count of all values "<R1" in col H. If names are repeated in sheet1, I do want to include the multiple occurences in my count. Thanks in advance. I hope this is easy for you while at the same time not something I could have come up with on my own! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a count from multiple lookups
One way ..
Assuming Sheet2's data in cols B and H is within row2 - row20 In Sheet1, assume names are running in A2 down Put in say, B2: =IF(A2="","",SUMPRODUCT((Sheet2!$B$2:$B$20=A2)*(Sh eet2!$H$2:$H$20<"R1"))) Copy down Adapt the ranges to suit, but note that we can't use entire col references in SUMPRODUCT (eg: B:B, H:H) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "andy62" wrote: On sheet1 I have a column with multiple names. I need a function that can look up all those names from column B of sheet2, and then count the occurance of a value in column H of sheet2. Here's the example: Sheet1 (note that some rows are blank, and some names may appear 1 time): Helen Back Cyndi Lou Who Cyndi Lou Who Sam Spade Sheet2 (names only appear once): col B col H Cyndi Lou Who R2 Dan Danger R1 Dou Luva R3 Hal Brook R2 Helen Back R1 Joe Mama R2 Lou Duva R2 Mai Tai R2 Roberta Flack R3 Ron Popeil R2 Sam Spade R2 Will Not R2 Yoda n/a So I need a formula that can do lookup sheet1's names in col B of sheet2, then give me a count of all values "<R1" in col H. If names are repeated in sheet1, I do want to include the multiple occurences in my count. Thanks in advance. I hope this is easy for you while at the same time not something I could have come up with on my own! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a count from multiple lookups
Thanks, but I may not have been clear that I need a single formula, located
in a cell over on a sheet3, to provide me with the total. I'd prefer not to add an extra column to sheet1 to give me a count for each row. Is it possible to have a single formula that can directly count all those lookups? "Max" wrote: One way .. Assuming Sheet2's data in cols B and H is within row2 - row20 In Sheet1, assume names are running in A2 down Put in say, B2: =IF(A2="","",SUMPRODUCT((Sheet2!$B$2:$B$20=A2)*(Sh eet2!$H$2:$H$20<"R1"))) Copy down Adapt the ranges to suit, but note that we can't use entire col references in SUMPRODUCT (eg: B:B, H:H) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "andy62" wrote: On sheet1 I have a column with multiple names. I need a function that can look up all those names from column B of sheet2, and then count the occurance of a value in column H of sheet2. Here's the example: Sheet1 (note that some rows are blank, and some names may appear 1 time): Helen Back Cyndi Lou Who Cyndi Lou Who Sam Spade Sheet2 (names only appear once): col B col H Cyndi Lou Who R2 Dan Danger R1 Dou Luva R3 Hal Brook R2 Helen Back R1 Joe Mama R2 Lou Duva R2 Mai Tai R2 Roberta Flack R3 Ron Popeil R2 Sam Spade R2 Will Not R2 Yoda n/a So I need a formula that can do lookup sheet1's names in col B of sheet2, then give me a count of all values "<R1" in col H. If names are repeated in sheet1, I do want to include the multiple occurences in my count. Thanks in advance. I hope this is easy for you while at the same time not something I could have come up with on my own! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a count from multiple lookups
I have a very similar problem. I have attached the document below. On page two, in cell C:3, I want to be able to be able to show a percentage. My goal is to look at each record in the Game Play Sheet and compare colums C and J. I want the percentage of times that column C has a 1T, 1S, 1M, or 1L and J has a P* (any number or letter combo after it)... Make any sense? I've tried to use "Countif" with "Counta" and SumProduct....a couple IF statements....but to no avail. One of my biggest challenges is that when I try to search multiple records I can't use a wild card in the search pattern...and it's driving me nuts.... (i.e. Countif (!GamePlaySheet:C11:C79="1*") ... any help? +-------------------------------------------------------------------+ |Filename: Test Page.zip | |Download: http://www.excelforum.com/attachment.php?postid=5014 | +-------------------------------------------------------------------+ -- bmstar ------------------------------------------------------------------------ bmstar's Profile: http://www.excelforum.com/member.php...o&userid=36264 View this thread: http://www.excelforum.com/showthread...hreadid=560522 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a count from multiple lookups
try using the find command, something like if(not(isna(find(1T))), ... -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=560522 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a count from multiple lookups
.. I want the percentage of times that column C has a 1T, 1S, 1M, or 1L
and [col J] has a P* (any number or letter combo after it)... Hazarding a guess, perhaps something like this .. In sheet: 1st Down Dashboard, Try in C3: =SUMPRODUCT((ISNUMBER(SEARCH("P",'Game Play Sheet'!J11:J100)))*(ISNUMBER(SEARCH(1,'Game Play Sheet'!C11:C100))))/MIN(COUNTA('Game Play Sheet'!C11:C100),COUNTA('Game Play Sheet'!J11:J100)) Format C3 as percentage (dp to taste) The numerator SUMPRODUCT(...) returns the required counts satisfying criteria in both cols C and J, while the denominator MIN(...) returns the total "completed" plays in both cols C and P (assuming "completed" plays are where both cols C and P contain inputs) Replace SEARCH with FIND if you need it to be case sensitive (SEARCH is not case sensitive). Adapt the ranges to suit .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "bmstar" wrote: I have a very similar problem. I have attached the document below. On page two, in cell C:3, I want to be able to be able to show a percentage. My goal is to look at each record in the Game Play Sheet and compare colums C and J. I want the percentage of times that column C has a 1T, 1S, 1M, or 1L and J has a P* (any number or letter combo after it)... Make any sense? I've tried to use "Countif" with "Counta" and SumProduct....a couple IF statements....but to no avail. One of my biggest challenges is that when I try to search multiple records I can't use a wild card in the search pattern...and it's driving me nuts.... (i.e. Countif (!GamePlaySheet:C11:C79="1*") ... any help? +-------------------------------------------------------------------+ |Filename: Test Page.zip | |Download: http://www.excelforum.com/attachment.php?postid=5014 | +-------------------------------------------------------------------+ -- bmstar ------------------------------------------------------------------------ bmstar's Profile: http://www.excelforum.com/member.php...o&userid=36264 View this thread: http://www.excelforum.com/showthread...hreadid=560522 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a count from multiple lookups
Thanks Max. I appreciate your time and offering of your ability! One more Question..... Can I use wildcards in the search function or search for an {array} of criteria? Thanks again! Ben -- bmstar ------------------------------------------------------------------------ bmstar's Profile: http://www.excelforum.com/member.php...o&userid=36264 View this thread: http://www.excelforum.com/showthread...hreadid=560522 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a count from multiple lookups
"andy62" wrote:
Thanks, but I may not have been clear that I need a single formula, located in a cell over on a sheet3, to provide me with the total. I'd prefer not to add an extra column to sheet1 to give me a count for each row. Is it possible to have a single formula that can directly count all those lookups? If it were not for your requirement: If names are repeated in sheet1, I do want to include the multiple occurences in my count. then I think we could use in a cell in Sheet3: =SUMPRODUCT(ISNUMBER(MATCH(Sheet2!$B$2:$B$20,Sheet 1!$A$2:$A$100,0))*(Sheet2!$H$2:$H$20<"R1")) [The above returns the conditional count, but only for the unique items within Sheet1!$A$2:$A$100. It'll return 2 for the sample data posted, instead of the required 3] Afraid I'm out of further suggestions for you. But do hang around awhile for better insights from others to flow in. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a count from multiple lookups
Thanks, Max, I may get some use from your formula for another statistic I
have to compile. And I learned something about how to use MATCH and ISNUMBER in a SUMPRODUCT function. But since my count is of the role the people are in rather than the people themselves, I would need to count all instances. "Max" wrote: "andy62" wrote: Thanks, but I may not have been clear that I need a single formula, located in a cell over on a sheet3, to provide me with the total. I'd prefer not to add an extra column to sheet1 to give me a count for each row. Is it possible to have a single formula that can directly count all those lookups? If it were not for your requirement: If names are repeated in sheet1, I do want to include the multiple occurences in my count. then I think we could use in a cell in Sheet3: =SUMPRODUCT(ISNUMBER(MATCH(Sheet2!$B$2:$B$20,Sheet 1!$A$2:$A$100,0))*(Sheet2!$H$2:$H$20<"R1")) [The above returns the conditional count, but only for the unique items within Sheet1!$A$2:$A$100. It'll return 2 for the sample data posted, instead of the required 3] Afraid I'm out of further suggestions for you. But do hang around awhile for better insights from others to flow in. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a count from multiple lookups
You're welcome, Andy.
Try a fresh post if nobody drops by here for the single cell formula that you seek. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "andy62" wrote: Thanks, Max, I may get some use from your formula for another statistic I have to compile. And I learned something about how to use MATCH and ISNUMBER in a SUMPRODUCT function. But since my count is of the role the people are in rather than the people themselves, I would need to count all instances. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Total or Count based on multiple conditions | Excel Discussion (Misc queries) | |||
Multiple Variable Count problem | Excel Worksheet Functions | |||
how do I count with multiple arguments | Excel Worksheet Functions | |||
Advanced unique cell count with multiple conditions ... help! | Excel Worksheet Functions | |||
Count rows based on multiple criteria | Excel Worksheet Functions |