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

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
 
T

Teethless mama

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

ctrl+shift+enter, not just enter
 
T

Teethless mama

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

ctrl+shift+enter, not just enter
 
G

Glenn

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

Glenn

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

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

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

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to count letter B based on data in other columns 6
Count Unique Values in 1 Column based on Date Range in another Column 28
count unique in one column based on two other columns 4
Count the times a value appears in a column based on another value 6
count value dependent on unique or repeated values in another colu 2
Sum one column based on the unique value in another column 2
Lookup in a matrix (with x and y headings) based on value x and y 2
Counting unique items in a list based on a condition 4

Top