| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count letter B based on data in other columns
Hello...I did ask the below question 3 days ago and I got answer from you.
Now I need to add something to my below question In the below formula you gave me, we count letter B based on unique value among duplicates 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 has no date like 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 fast feedback Mero "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 A 5020598707 A 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
|
|||
|
|||
|
How to count letter B based on data in other columns
Mero,
Again, array enter: =SUM(N(FREQUENCY(IF((rngF<"00.00.0000")*(rngJ="B" ),MATCH(rngD&"",rngD&"",)),MATCH(rngD&"",rngD&"",) )0)) Though you may need to use something like this if the dates are true dates and not strings: =SUM(N(FREQUENCY(IF((TEXT(rngF,"dd.mm.yyyy")<"00. 00.0000")*(rngJ="B"),MATCH(rngD&"",rngD&"",)),MATC H(rngD&"",rngD&"",))0)) HTH, Bernie MS Excel MVP "Mero" wrote in message ... Hello...I did ask the below question 3 days ago and I got answer from you. Now I need to add something to my below question In the below formula you gave me, we count letter B based on unique value among duplicates 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 has no date like 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 fast feedback Mero "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 A 5020598707 A 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
|
|||
|
|||
|
Please help: How to count letter B based on data in other columns
Please support me in the below issue ASAP
"Mero" wrote: Hello...I did ask the below question 3 days ago and I got answer from you. Now I need to add something to my below question In the below formula you gave me, we count letter B based on unique value among duplicates 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 has no date like 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 fast feedback Mero "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 A 5020598707 A 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count letter B based on data in other columns
Thanks a million.....it worked perfectly -- Marso ------------------------------------------------------------------------ Marso's Profile: http://www.thecodecage.com/forumz/member.php?userid=339 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=98906 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count letter B based on data in other columns
Assuming Column F contain real Excel dates
=SUM(N(FREQUENCY(IF((rngJ="B")*(rngF0),MATCH(rngD &"",rngD&"",)),MATCH(rngD&"",rngD&"",))0)) ctrl+shift+enter, not just enter "Mero" wrote: Hello...I did ask the below question 3 days ago and I got answer from you. Now I need to add something to my below question In the below formula you gave me, we count letter B based on unique value among duplicates 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 has no date like 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 fast feedback Mero "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 A 5020598707 A 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count letter B based on data in other columns
Hi Teethless,
Thanks for yourr support bu the below formula count the letter B with date 00.00.0000.....I dont know why. "Teethless mama" wrote: Assuming Column F contain real Excel dates =SUM(N(FREQUENCY(IF((rngJ="B")*(rngF0),MATCH(rngD &"",rngD&"",)),MATCH(rngD&"",rngD&"",))0)) ctrl+shift+enter, not just enter "Mero" wrote: Hello...I did ask the below question 3 days ago and I got answer from you. Now I need to add something to my below question In the below formula you gave me, we count letter B based on unique value among duplicates 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 has no date like 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 fast feedback Mero "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 A 5020598707 A 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count letter B based on data in other columns
Many Thanks Bernie! it is working
"Bernie Deitrick" wrote: Mero, Again, array enter: =SUM(N(FREQUENCY(IF((rngF<"00.00.0000")*(rngJ="B" ),MATCH(rngD&"",rngD&"",)),MATCH(rngD&"",rngD&"",) )0)) Though you may need to use something like this if the dates are true dates and not strings: =SUM(N(FREQUENCY(IF((TEXT(rngF,"dd.mm.yyyy")<"00. 00.0000")*(rngJ="B"),MATCH(rngD&"",rngD&"",)),MATC H(rngD&"",rngD&"",))0)) HTH, Bernie MS Excel MVP "Mero" wrote in message ... Hello...I did ask the below question 3 days ago and I got answer from you. Now I need to add something to my below question In the below formula you gave me, we count letter B based on unique value among duplicates 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 has no date like 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 fast feedback Mero "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 A 5020598707 A 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 | |||
| Count letter"B" in one column based on unique value among duplicat | Excel Worksheet Functions | |||
| COUNT based on conditions in 2 or more columns | Excel Worksheet Functions | |||
| Count based on criteria from two different columns | Excel Discussion (Misc queries) | |||
| Count of Data Based on Multiple Columns | Excel Worksheet Functions | |||
| formula to count based on data in two different cells/columns | Excel Discussion (Misc queries) | |||
Linear Mode
