Count Unique values

[jskushawah]

please write down a formula to count unique values in col A:

10269

10272

10272

10279

10257

10266

10270

10261

10288

10266

10258

10263

10286

10279

10274

10272

10274

10265

10278

10282

10282

10290

10268

10287

10283

10256

10258

10272

10289

10263

10282

10271

10264

10272

10259

10264

10290

10277

10263

10264

10255

10278

10266

10264

10289

10286

10259

10283

10273

10286

10264

10263

10267

10259

10262

10288

10262

10283

10271

10267

10259

10265

10261

10282

10270

10269

10260

10288

10260

10257

10264

10283

10258

10265

10269

10265

10263

10260

10282

10264

10273

10285

10269

10288

10263

10286

10276

10257

10282

10279

10285

10263

10286

10258

10284

10279

10262

10278

10290

10264

10259

10258

10268

10266

10266

10282

10269

10279

10280

10267

10264

10280

10259

10272

10277

10274

10276

10278

10285

10270

10265

10288

10276

10270

10255

10280

10283

10271

10261

10285

10275

10270

10290

10270

10266

 

[NARAYANK991]

Hi Jitendra ,


Try this :


=SUM(IF(FREQUENCY(List_of_numbers,List_of_numbers)>=1,1,0))


or this :


=SUM(--(FREQUENCY(List_of_numbers,List_of_numbers)>=1))


where List_of_numbers is your range of data , e.g. A2:A136


Narayan

 

[jskushawah]

Thanks Narayan but it gives wrong result...

in the below list we have only 8 unique values but both formulas return result 18...

111

103

111

104

110

105

110

105

111

113

105

115

113

102

107

112

109

120

117

104

120

100

106

108

101

103

120

105

108

114

101

 

[NARAYANK991]

Hi Jitendra ,


I think I misunderstood your question ; when you say you want unique value count , do you mean to say that only those values which occur exactly once should be counted ?


If so , change the sign from >= to just =.


In the list you have given in your second post , there are 9 unique values.


Narayan

 

[jskushawah]

yes it works !!!!

Thanks Narayan ...

 

[bobhc]

Good day Jitendra


Using plain old CF I get 9 unique values


111

103

111

104

110

105

110

105

111

113

105

UV--115

113

UV--102

UV--107

UV--112

UV--109

120

UV--117

104

120

UV--100

UV--106

108

101

103

120

105

108

UV--114

101

 

[supero]

{=SUM(IF(COUNTIF($A$1:$A$135,$A$1:$A$135)=1,1,0))} (array formula)


this should work.

Result = 4

 

[jskushawah]

Yes Supero...

actually i was looking such an array formula ..

thanks for your revert...