Count number of occurences

collinsc

Board Regular
Joined
Sep 13, 2006
Messages
230
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!
 

Some videos you may like

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
Joined
Feb 28, 2003
Messages
2,615
Hi,

Go to Format > Conditional Formatting > Formula Is

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

change the format.

HTH
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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
Joined
Sep 13, 2006
Messages
230
most odd... yes and yes to both of those...
it is written as "text" does that make a difference?
 

collinsc

Board Regular
Joined
Sep 13, 2006
Messages
230

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,803
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 13, 2006
Messages
230
hmm - can i add this as a "conditional format"? i havent been able to do so...
 

Watch MrExcel Video

Forum statistics

Threads
1,114,676
Messages
5,549,374
Members
410,911
Latest member
AniEx
Top