Help with a formula please

M

Matt Stone

I need to count a number of text cells to find out if an exact same
occurance occurs more than twice.
I Use Excel 2003

Any ideas

Thanks
 
F

FSt1

hi matt,
try this...

=countif(A1:A50000,"yourtext")

works in xl2k. lookup countif in xl help.
Adjust the formula to fit your data.

regards

FSt1
 
J

Jim May

If in Range a1:a100 you say have abc appearing 5 times
Try in cell C1 =Countif(A1:A100,D1)
where A1:A100 is all your text
Enter into D1 >> abc
C1 should bring back 5
 
B

Bryan Hessey

Assuming your data is in column A from cell A1 downwards,

in B1 enter

=IF(COUNTIF(A$1:A$8,A1)>2,"More than two "&A1,"")

using CTRL/Shift/Enter to enter an array formula, then formula-drag
this to the bottom of your data.
 
M

Matt Stone

Perhaps I did not explain it right. I have a column of different text of
which contain 3 characters (Approx 11 rows). I need to make sure that of
these none of them appear more than two times:

Example.

Column A
che
man
ars
mcy
eve
asv
ars
bir
whu
wba
liv

I need a sum to make sure the above text does not appear more than twice.
Bearing in mind I have multiple columns with different text in each.

Thanks
 
J

Jim May

With your given data in A1:A11 in cell B1 enter:
=COUNTIF($A$1:$A$11,A1)
and Copy (B1) down to B11.
Note amts greater than 1 !!
 
Top