highlighting if specific number appears multiple times

[Rachy_bo]

Hi guys,
very new to excel!
I was wondering if there is a formula you can use to highlight if a specific word or number is repeated over a certain amount of times e.g. I want to highlight the cells that contain the number 19 only if it has appeared in the whole spreadsheet 3 times
basically there is only so many 19's that can be given out so if its typed in again it needs to flag up as red or with a warning of some kind,

thanks
rach

 

[Joe4]

Welcome to the Board!

You can use Conditional Formatting to do this.
Let's say that you wanted to check the range A1:Z100.
Then, you could select than range and apply this Conditional Formatting formula to it:

=AND(A1=19,COUNTIF($A$1:$Z$100,19)=3)

and choose your desired formatting color.

 

[Rachy_bo]

Welcome to the Board!

You can use Conditional Formatting to do this.
Let's say that you wanted to check the range A1:Z100.
Then, you could select than range and apply this Conditional Formatting formula to it:

=AND(A1=19,COUNTIF($A$1:$Z$100,19)=3)

and choose your desired formatting color.

Hi!
thankyou so much but i tried the code except I tried it with a date ''19.10.21'' and it didn't highlight anything, maybe i put it in wrong?
I put =AND(A1="19.10.21",COUNTIF($A$1:$Z$200,"19.10.21")=3)

Thanks
rach

 

[Joe4]

Is "19.10.21" a string, or is it a formatted date?
Does it appear EXACTLY 3 times in your range?
Did you select that exact range ($A$1:$Z$200) before applying the Conditional Formatting?

 

[Rachy_bo]

Is "19.10.21" a string, or is it a formatted date?

Its meant to be a date but its just typed in the cell as 19.10.21

 

[Rachy_bo]

Is "19.10.21" a string, or is it a formatted date?
Does it appear EXACTLY 3 times in your range?
Did you select that exact range ($A$1:$Z$200) before applying the Conditional Formatting?

You're a genius Joe,
i just figured out there was more than 3 of the same date so i just switched it to >3 in the formula
you are a lifesaver!
thankyou so much!

 

[Joe4]

You are welcome.

I originally had it as ">=3", but I re-read your question, and it looks like you were asking for exactly 3, so I changed it.

 

[Rachy_bo]

You are welcome.

I originally had it as ">=3", but I re-read your question, and it looks like you were asking for exactly 3, so I changed it.

I'm too new to this, i felt proud just by figuring out how to highlight cells with certain words in, so I need to go lie down after this :D
I would of never of thought of that formula, so you've made my life so much easier!
thankyou!