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

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

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

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

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