Count Duplicat only one time

[ali zaib]

Your support is request to guide can we count duplicate values just one time like as below

Ali
Zaib
Idrees
Ali

The answer should be like = 3 as the "Ali" is show two time

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$5<>"",MATCH($A$2:$A$5,$A$2:$A$5,0)),ROW($A$2:$A$5)-ROW($A$2)+1),1))

 

[Misca]

If your data is in A1:A4 try something like
=SUMPRODUCT((A1:A4<>"")*(1/COUNTIF(A1:A4,A1:A4)))

 

[ali zaib]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$5<>"",MATCH($A$2:$A$5,$A$2:$A$5,0)),ROW($A$2:$A$5)-ROW($A$2)+1),1))

Hi Sir,
Why we use Ctrl+Shift+Enter Kindly guide about it

 

[Aladin Akyurek]

Hi Sir,
Why we use Ctrl+Shift+Enter Kindly guide about it

Because it's an array-processing formula, a formula kind which by design requires control+shift+enter in the current versions of Excel.


By the way, the frequency formulation for unique count is faster than other formulations.

 

[ali zaib]

Thanks Sir for your support

 

[Aladin Akyurek]

You are welcome.

 

[ali zaib]

Hi sir,
Kindly guide about frequency function please

 

[Aladin Akyurek]

Hi sir,
Kindly guide about frequency function please

Post #2 contains the frequency formula for unique count (Duplicates counted just once.).


Here some links:

https://www.mrexcel.com/forum/excel-questions/633564-counting-duplicates-using-sum-if-frequency-match-2.html#post3156949

https://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html