count ?

[pierre]

hello guys...please help

A
1 max
2 max
3 lyr
4 vop
5 vop
6 vop

i would like to make the following formula:
" if ANY item of column A is present more than 1 time , count it for me "

THANK YOU

 

[Gary''s Student]

In B1 enter 0
In B2 enter:

=IF(COUNTIF($A$1:A2,A2)=2,1,0)+B1 and copy down

the last cell in column B will give the number of duplicated items in column
A (in your case 2)

 

[Prashant Runwal]

write following formula in cell B1 (in fronth of first max)
=countif($a$1:$a$6,$a1). Copy this formula till b6 and you will get desired
result. You can alter the countif range as per your actual data. If your
actual data is till 100 rows then formula should be
=countif($a$1:$a$100,$a1).

 

[daddylonglegs]

I assume that the result for your example should be 2. Try this formula

=SUM(IF(FREQUENCY(IF(A1:A100<>"",MATCH(A1:A100,A1:A100,0)),ROW(A1:A100)-ROW(A1)+1)>1,1))

confirmed with CTRL+SHIFT+ENTER

extend range if necessary