# Count number of occurences

#### collinsc

##### Board Regular
Hi All

At work we have a score prediction comp. 1-1 is the most common picked score and so a rule has been set that you cannot pick this score line more than 6 times.

He types all the scores in in columns i.e.

wat v man u 1-1 2-0 3-1
man c v che 1-0 2-1 4-0

How can i set it so that it notifies him that more than 6 occurences of 1-1 have been entered in a column?

Thanks!

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### Krishnakumar

##### Well-known Member
Hi,

Go to Format > Conditional Formatting > Formula Is

=(LEN(A1)-LEN(SUBSTITUTE(A1,"1-1","")))/3>6

change the format.

HTH

Thanks

#### collinsc

##### Board Regular
done that... it doesnt come up with an error message or anything....

#### Richard Schollar

##### MrExcel MVP

Hi Collinsc

Kris's formula works for me - are all the scores entered into the same column? Did you amend the format to be applied if the condition was met (eg colouring the cell red)?

Richard

#### collinsc

##### Board Regular
most odd... yes and yes to both of those...
it is written as "text" does that make a difference?

#### collinsc

##### Board Regular

i left this sheet for a while- to concentrate on my pivot nightmare!
i still cant do this... does it matter that it is formatted as text?

#### Jon von der Heyden

##### MrExcel MVP, Moderator
Hi,

Kris's formula works provided the entire string is in the cell. If the scores are in different cells spanning across the columns then perhaps something like:
Code:
``=COUNTIF(B2:J2,"1-1")>6``
Where the scores are inputted between column B and J (adjust ranges to suite). Again to be applied using conditional formatting.

Regards,
Jon

#### collinsc

##### Board Regular
ah- its probably the way i explained it..!
thanks for that Jon

#### collinsc

##### Board Regular
hmm - can i add this as a "conditional format"? i havent been able to do so...

Replies
1
Views
111
Replies
23
Views
421
Replies
5
Views
210
Replies
2
Views
45
Replies
8
Views
144