Count number of occurences

[collinsc]

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!

 

[Krishnakumar]

Hi,

Go to Format > Conditional Formatting > Formula Is

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

change the format.

HTH

 

[collinsc]

Thanks

 

[collinsc]

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

 

[Richard Schollar]

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]

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

 

[collinsc]

sorry to re-open this thread-
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]

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:

=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]

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

 

[collinsc]

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