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

  • Thread starter Thread starter Mero
  • Start date Start date
M

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
 
=SUM(N(FREQUENCY(IF(rngJ="B",MATCH(rngD&"",rngD&"",)),MATCH(rngD&"",rngD&"",))>0))

ctrl+shift+enter, not just enter
 
=SUM(N(FREQUENCY(IF(rngJ="B",MATCH(rngD&"",rngD&"",)),MATCH(rngD&"",rngD&"",))>0))

ctrl+shift+enter, not just enter
 
Mero said:
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
Click to expand...


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"))
 
Mero said:
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
Click to expand...


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
Click to expand...

There are 2 duplicate number entries that correspond to "B":
8020249409...B = 1
8020249409...B
8020249450...B = 2
8020249450...B
5020598429...B
5020598707...B
Click to expand...

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.
 
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
Click to expand...

There are 2 duplicate number entries that correspond to "B":
8020249409...B = 1
8020249409...B
8020249450...B = 2
8020249450...B
5020598429...B
5020598707...B
Click to expand...

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.
 
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
 
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
 
Back
Top