Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count letter"B" in one column based on unique value among duplicat
Hello, Need to know how to count the letter "B" in column J based on unique
values among duplicates in another column. Column D Column J 5020598285 A 5020598286 A 5020598287 A 5020598288 A 5020598563 A 8020249197 C 8020249198 C 8020249409 B 8020249409 B 8020249450 B 8020249450 B 5020598429 B 5020598707 B If you have a look at records in column D, you will find that some records are duplicated. If I count letter "B", I will get 4 but actually they are only 2 records for letter "B" as 8020249409 and 8020249409 are duplicated. I need a function to count letter "B" without duplication. Hope I've explained my problem. Your fast support is really appreciated. Many Thanks Mero |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count letter"B" in one column based on unique value among duplicat
=SUM(N(FREQUENCY(IF(rngJ="B",MATCH(rngD&"",rngD&"" ,)),MATCH(rngD&"",rngD&"",))0))
ctrl+shift+enter, not just enter "Mero" wrote: Hello, Need to know how to count the letter "B" in column J based on unique values among duplicates in another column. Column D Column J 5020598285 A 5020598286 A 5020598287 A 5020598288 A 5020598563 A 8020249197 C 8020249198 C 8020249409 B 8020249409 B 8020249450 B 8020249450 B 5020598429 B 5020598707 B If you have a look at records in column D, you will find that some records are duplicated. If I count letter "B", I will get 4 but actually they are only 2 records for letter "B" as 8020249409 and 8020249409 are duplicated. I need a function to count letter "B" without duplication. Hope I've explained my problem. Your fast support is really appreciated. Many Thanks Mero |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count letter"B" in one column based on unique value among duplicat
Mero wrote:
Hello, Need to know how to count the letter "B" in column J based on unique values among duplicates in another column. Column D Column J 5020598285 A 5020598286 A 5020598287 A 5020598288 A 5020598563 A 8020249197 C 8020249198 C 8020249409 B 8020249409 B 8020249450 B 8020249450 B 5020598429 B 5020598707 B If you have a look at records in column D, you will find that some records are duplicated. If I count letter "B", I will get 4 but actually they are only 2 records for letter "B" as 8020249409 and 8020249409 are duplicated. I need a function to count letter "B" without duplication. Hope I've explained my problem. Your fast support is really appreciated. Many Thanks Mero If I count the letter "B" in your column D, I get 6. If you want only the unique combinations of column D plus column J where column J = "B", use this: =SUMPRODUCT((B1:B13<"")/COUNTIF(B1:B13,B1:B13&"")*(J1:J13="B")) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count letter"B" in one column based on unique value among duplicat
Not clear what you want to do.
There are 4 unique number entries that correspond to "B": 8020249409...B = 1 8020249409...B 8020249450...B = 2 8020249450...B 5020598429...B = 3 5020598707...B = 4 There are 2 duplicate number entries that correspond to "B": 8020249409...B = 1 8020249409...B 8020249450...B = 2 8020249450...B 5020598429...B 5020598707...B You said the answer you're looking for is 2 so I'm assuming you want the second scenario? Array entered** : =COUNT(1/FREQUENCY(IF((COUNTIF(D2:D14,D2:D14)1)*(J2:J14="B "),D2:D14),D2:D14)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Mero" wrote in message ... Hello, Need to know how to count the letter "B" in column J based on unique values among duplicates in another column. Column D Column J 5020598285 A 5020598286 A 5020598287 A 5020598288 A 5020598563 A 8020249197 C 8020249198 C 8020249409 B 8020249409 B 8020249450 B 8020249450 B 5020598429 B 5020598707 B If you have a look at records in column D, you will find that some records are duplicated. If I count letter "B", I will get 4 but actually they are only 2 records for letter "B" as 8020249409 and 8020249409 are duplicated. I need a function to count letter "B" without duplication. Hope I've explained my problem. Your fast support is really appreciated. Many Thanks Mero |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count letter"B" in one column based on unique value among dupl
Hello...Need your kind support again :)
In the below formula you gave me, we count letter B based on unique value in column D. Now, I added another column F which contains dates. I need to count letter B based on unique value in column D and that have no date format 00.00.0000 in column F. Please See below 3 columns, the result should be only 1 B Hope I've explained my problem I dont know how to thank you for this support Awaiting your feedback "Teethless mama" wrote: =SUM(N(FREQUENCY(IF(rngJ="B",MATCH(rngD&"",rngD&"" ,)),MATCH(rngD&"",rngD&"",))0)) ctrl+shift+enter, not just enter "Mero" wrote: Hello, Need to know how to count the letter "B" in column J based on unique values among duplicates in another column. Column D Column J Column F 5020598285 A 5020598286 A 5020598287 A 5020598288 A 5020598563 A 8020249197 C 8020249198 C 8020249409 B 00.00.0000 8020249409 B 00.00.0000 8020249450 B 13.01.2009 8020249450 B 13.01.2009 5020598429 B 5020598707 B If you have a look at records in column D, you will find that some records are duplicated. If I count letter "B", I will get 4 but actually they are only 2 records for letter "B" as 8020249409 and 8020249409 are duplicated. I need a function to count letter "B" without duplication. Hope I've explained my problem. Your fast support is really appreciated. Many Thanks Mero |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I have a column of numbers and I need to put the letter "P" in fr. | Excel Discussion (Misc queries) | |||
how can I count if column A="active" and column E="Job" in a list? | Excel Worksheet Functions | |||
how can I count if column A="active" and column E="Job"? | Excel Worksheet Functions | |||
Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |