Countif no duplicates

[uwish7]

Is there a way to use the Countif function and remove duplicates

Countif FG Component
=Countif(C:C, c2) 22070090 300432

In the example below if I drag down the formlar , I will have the same number repeated. How do I remove it?

 

[salim hasan]

Try this formula

=IF(COUNTIF($C$2:C2,C2)=1,COUNTIF($C$2:$C$20,C2),"")

Book1
	C	D
2	1	2
3	2	1
4	8	2
5	1
6	4	1
7	5	1
8	8
9
Sheet1

Cell Formulas
Range	Formula
D2	=IF(COUNTIF($C$2:C2,C2)=1,COUNTIF($C$2:$C$20,C2),"")
D3	=IF(COUNTIF($C$2:C3,C3)=1,COUNTIF($C$2:$C$20,C3),"")
D4	=IF(COUNTIF($C$2:C4,C4)=1,COUNTIF($C$2:$C$20,C4),"")
D5	=IF(COUNTIF($C$2:C5,C5)=1,COUNTIF($C$2:$C$20,C5),"")
D6	=IF(COUNTIF($C$2:C6,C6)=1,COUNTIF($C$2:$C$20,C6),"")
D7	=IF(COUNTIF($C$2:C7,C7)=1,COUNTIF($C$2:$C$20,C7),"")
D8	=IF(COUNTIF($C$2:C8,C8)=1,COUNTIF($C$2:$C$20,C8),"")
D9	=IF(COUNTIF($C$2:C9,C9)=1,COUNTIF($C$2:$C$20,C9),"")

 

[Peter_SSs]

Try this formula

=IF(COUNTIF($C$2:C2,C2)=1,COUNTIF($C[COLOR="#FF0000"][B]$[/B][/COLOR]2:$C$20,C2),"")

A very minor thing but you could save a little processing by removing that red $
For example, in row 4 of your sample data, the fact that the logical test in your if statement is TRUE, means that there must not be any 8s above row 4 so there is no need to include rows 2 & 3 in the COUNTIF.
Of course the longer the list is and the further down the sheet the formula gets copied, the greater the saving when a new value is found.

 

[salim hasan]

A very minor thing but you could save a little processing by removing that red $
For example, in row 4 of your sample data, the fact that the logical test in your if statement is TRUE, means that there must not be any 8s above row 4 so there is no need to include rows 2 & 3 in the COUNTIF.
Of course the longer the list is and the further down the sheet the formula gets copied, the greater the saving when a new value is found.

You are right
Unintended mistake
Thanks