Conditionally format columns if there are 6 or less cells

[Milos]

Hi,

I am wanting to conditionally format columns if they have less than 6 of a certain entry. But have no idea how to start with this code.

In this example below I need the conditional format to apply to column B when there are less than 6 YES entries. Column A also needs to be dependent on the cell entry (e.g. Pizza, Hamburger, Chips). So in this example all the YES entries for the pizza would be conditionally formatted because column B has less than the 6 necessary YES entries.


[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]Food type[/TD]
[TD="align: center"]with cheese[/TD]
[/TR]
[TR]
[TD]Pizza[/TD]
[TD]YES
[/TD]
[/TR]
[TR]
[TD]Pizza[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]Pizza[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]Hamburger[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]Hamburger[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]Hamburger[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]Hamburger[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]Hamburger[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]Hamburger[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]Chips[/TD]
[TD]NO[/TD]
[/TR]
</tbody>[/TABLE]

All help is appreciated,
Thanks

 

[Joe4]

Let's say that your data is in range A2:B11. Then highlight cells B2:B11 and enter this Conditional Formatting formula:

=countifs($A$2:$A$11,$A2,$B$2:$B$11,"YES")<6

and choose your formatting color.