|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 21st, 2009, 04:49 PM
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
May 21st, 2009, 09:33 PM
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
May 21st, 2009, 10:50 PM
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
May 21st, 2009, 11:12 PM
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
May 22nd, 2009, 04:19 AM
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
May 22nd, 2009, 02:20 PM
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
May 22nd, 2009, 02:24 PM
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
