Count unique values among duplicates

[shasi.shekhar@gmail.com]

Guys please help me getting count unique values among duplicates.

Column

1186MPYes

1002PBYes

1003PBYes

1004PBNo

1005PBNo

1007PBYes

1017PBYes

1020PBYes

1021PBYes

1022PBYes

1025PBYes

1031UPYes

1032PBYes

1038HRYes

1042HRYes

1043PBYes

1045PBYes

1046PBYes

1047PBYes

1049HRYes

1051HRYes

1052HRYes

1054RJYes

1057PBYes

1058PBYes

1059PBYes

1071PBYes

1072HRYes

1073PBYes

1074NDYes

1077NDYes

1078NDYes

1079PBYes

1081PBYes

1082UPYes

1087NDYes

1089HRYes

1091NDYes

1092PBYes

1094PBYes

1095HRYes

1099PBYes

1103HRYes

1104PBYes

1105UTYes

1106UTYes

1107NDYes

1108UPYes

 

[Hui]

Shasi


Firstly Welcome to the Chandoo.org forums


Name your data area Data

The array enter this anywhere

=SUM(1/COUNTIF(Data,Data)) Ctrl, Shift , Enter

 

[shasi.shekhar@gmail.com]

Thanks Hui for your response, but by taking above data the formula gives 48 for all rows.

 

[Luke M]

Shasi,

Don't copy the formula down/across. It's a single-output formula, meant to give a unique count. The data you posted is all unique. What number were you expecting?

 

[shasi.shekhar@gmail.com]

Hui, How can I share excel sheet with you, so that result I want can be understood by you.

Thanks,

 

[vasim]

http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[shasi.shekhar@gmail.com]

I have uploaded the Demo Sheet and also explained the result I need.


https://rapidshare.com/files/961030234/Demo_Sheet.xls


Thanks in Advance for your support

 

[Hui]

I don't want to be critical but there's a whole Column "State" you didn't tell us about?


Name the two sets of data, State and DD

Then use:

=SUMPRODUCT((State="PB")*(1/COUNTIF(DD,DD)))

or

=SUMPRODUCT((State=$D$2)*(1/COUNTIF(DD,DD)))

where the States initials are in D2

 

[shasi.shekhar@gmail.com]

Hui,


I am really apologetic for miscommunication from my side.

Your solution is working well.

Many Thanks for support.

 

[Guest]

used trucks

 

[prasaddn]

Hi Hui, All,


how can I use the formula:

=SUM(1/COUNTIF(Data,Data)) Ctrl, Shift , Enter


in a my defined name?


Regards,

Prasad DN

 

[prasaddn]

Got it


I have used sumproduct instead of sum. This is not CSE formula..


=SUMPRODUCT(1/COUNTIF(Data,Data))


Well, but for future reference, can defined names have CSE formulas ?


Please advise.


Regards,

Prasad DN

 

[SirJB7]

Hi, prasaddn!

Entered as an array formula, it gives you the number of different elements in range Data.

Entered as a normal formula, it gives you the percentage of occurrence of a certain value within its equals (that's to say 1/n, where n is the number of times that element appears).

Let Hui's correct me (once more time) if I'm wrong.

Regards!

 

[prasaddn]

Are you referring to sum() or sumproduct() ?

 

[SirJB7]

To SUM, I only read your first post.