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

=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

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"))

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

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