Count letter"B" in one column based on unique value among duplicat

[Mero]

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

 

[Teethless mama]

=SUM(N(FREQUENCY(IF(rngJ="B",MATCH(rngD&"",rngD&"",)),MATCH(rngD&"",rngD&"",))>0))

ctrl+shift+enter, not just enter

 

[Glenn]

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

 

[T. Valko]

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(D214,D214)>1)*(J2:J14="B"),D214),D214))

** 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.

 

[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