# Using Countif with Conditional Formatting



## SingTuba (Dec 18, 2022)

Hello, I am trying to make a sheet that highlights if there are clash between Exam schedules. I can use Countif to check if there are more than one exact slots in a row and highlight the exact cells with Conditional Formatting.
But when I use countif to check overlapping time slots the formula works but Conditional Formatting highlights the entire row. Is it possible for Conditional Formatting to work with my Overlapping Countif formula?.

Book1.xlsxABCDEFGHIJ12StudentsExam and TimeOverlap between two exact Slots Overlap between Monday Slot 1 and Monday Slot 1.5Overlap between Tuesday Slot 1 and Tuesday Slot 1.5Legend3AmeliaEnglishMathsArtHistorySlot 1 = 10:00 AM -11:30 AM 4Time SlotMonday Slot 1Tuesday Slot 1Monday Slot 1Tuesday Slot 2TRUEFALSEFALSESlot 1.5 = 10:00 AM - 12:00 PM5Jayden LiteratureMathsMusicGeographySlot 2 = 11:30 AM-1:00 PM6Time SlotMonday Slot 1Tuesday Slot 1Monday Slot 1.5Tuesday Slot 2FALSETRUEFALSESlot 2.5 = 12:00 PM -1:30 PMSheet1Cell FormulasRangeFormulaF4F4=COUNTIF($B$4:$E$4,B4)>1G4G4=AND(COUNTIF($B$4:$E$4,"Monday Slot 1"),COUNTIF($B$4:$E$4,"Monday Slot 1.5"))H4H4=AND(COUNTIF($B$4:$E$4,"Tuesday Slot 1"),COUNTIF($B$4:$E$4,"Tuesday Slot 1.5"))F6F6=COUNTIF($B$6:$E$6,B6)>1G6G6=AND(COUNTIF($B$6:$E$6,"Monday Slot 1"),COUNTIF($B$6:$E$6,"Monday Slot 1.5"))H6H6=AND(COUNTIF($B$6:$E$6,"Tuesday Slot 1"),COUNTIF($B$6:$E$6,"Tuesday Slot 1.5"))Cells with Conditional FormattingCellConditionCell FormatStop If TrueB6:E6Expression=COUNTIF($B$6:$E$6,"Monday Slot 1")+COUNTIF($B$6:$E$6,"Monday Slot 1.5")>1textNOB4:E4Expression=COUNTIF($B$4:$E$4,B4)>1textNO


----------



## Fluff (Dec 18, 2022)

How about
	
	
	
	
	
	



```
=COUNTIF($B$6:$E$6,B6)>1
```


----------



## SingTuba (Dec 18, 2022)

Fluff said:


> How about
> 
> 
> 
> ...


Hi, yes this formula works for two exact values but I need Conditional Formatting to highlight cells that have text in the Countif formula criteria. 
	
	
	
	
	
	



```
=COUNTIF($B$6:$E$6,"Monday Slot 1")+COUNTIF($B$6:$E$6,"Monday Slot 1.5")>1
```


----------



## Fluff (Dec 18, 2022)

How about
	
	
	
	
	
	



```
=AND(OR(B6="Monday Slot 1",B6="Monday Slot 1.5"),COUNTIF($B$6:$E$6,"Monday Slot 1"),COUNTIF($B$6:$E$6,"Monday Slot 1.5"))
```


----------



## SingTuba (Dec 19, 2022)

Fluff said:


> How about
> 
> 
> 
> ...


Wow this works perfectly, Thank You so much.


----------



## Fluff (Dec 19, 2022)

You're welcome & thanks for the feedback.


----------

