Countif Help

[Christopher Naveen]

Hi,

I have a col contains numbers and the col also have duplicates. Now i want
to count how many duplicates against 1 value. This i can do with countif
easily but the important point is if the 1st value has duplicates then it
shud say the count in the 1st value and all other duplicate values shud be
blank. pls. refer the below example for better understanding,

Values Count Remarks
100 3 (Count of 100 in the col is 3)
101 2 (Count of 101 in the col is 2)
100 (100 is duplicate so the count shud be blank)
100 (100 is duplicate so the count shud be blank)
101 (100 is duplicate so the count shud be blank)
102 2 (Count of 102 in the col is 2)
103 1 (Count of 103 in the col is 1)
102 (102 is duplicate so the count shud be blank)
555 1 (555 there is no duplicates in the col and the count is
1)

I hope the above example helps to understand better. I request any one of
you to help me in this requirement.

Thanks in Advance

-Chr

 

[Max]

Data in A1 down
In B1: =IF(COUNTIF(A$1:A1,A1)>1,"",COUNTIF(A:A,A1))
Copy down to compute exactly as indicated
Pl remember to press the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[Bob Phillips]

B2: =IF(COUNTIF($A$1:A1,A2),"",COUNTIF(A:A,A2))

copy down

 

[Pete_UK]

Try this in B2:

=IF(COUNTIF(A$2:A2,A2)=1,COUNTIF(A:A,A2),"")

and copy this down.

Hope this helps.

Pete

 

[Christopher Naveen]

Thank u so much max. it works fine.

Data in A1 down
In B1: =IF(COUNTIF(A$1:A1,A1)>1,"",COUNTIF(A:A,A1))
Copy down to compute exactly as indicated
Pl remember to press the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[Christopher Naveen]

Thank u so much bob. it works fine.

B2: =IF(COUNTIF($A$1:A1,A2),"",COUNTIF(A:A,A2))

copy down

--
__________________________________
HTH

Bob

 

[Christopher Naveen]

Thank u so much. it works fine.

Try this in B2:

=IF(COUNTIF(A$2:A2,A2)=1,COUNTIF(A:A,A2),"")

and copy this down.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Christopher - thanks for feeding back.

Pete

 

[Max]

Welcome, glad it did.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik