CountIF?

[Sysrq111]

Hi, I was wondering if somebody could help me.

I have a list of data I have imported, it is 7 numbers across and
continues down a long way.

I am trying to use the COUNTIF function but I'm not sure if this is
what I need.

I would like the spreadsheet to display when a number repeats itself
during this list.

For example -


1 2 3 4 5
7 8 9 6 2
3 5 1 4 11

I would like a function that shows when the 2 has repeated itself. I
then need to find how many times it has repeated itself.

Any help much appreciated.

Thanks.

 

[Don Guillett]

if you JUST want the one number
=countif(a2:c222,2)

 

[Sysrq111]

Thanks, but I was looking for a formula that would detect any repeat
of any number throughout the whole cell range.

 

[divakarprabhu2000]

Thanks, but I was looking for a formula that would detect any repeat
of any number throughout the whole cell range.

You can use conditional formatting. Suppose your area consist of
A1:C22 then select cell A1 and goto Format>Conditional Formatting. In
the resulting dialogue box select "Formula Is" from the first dropdown
box. In the second box enter =COUNTIF($A$1:$C$22,A1)>1. Click the
format button and select a proper format and click OK. Now copy cell
A1 and select the whole range (A1:C22) and paste special formats. Now
if any item which repeats with in this range is high lighted with the
specific format.
Regards,
Divakar