Highlight every 5th recurrence in a column for a given value/id

[Shivesh]

HI,

Can anyone help me finding a formula to Highlight every 5th recurrence in a column for a given value/id? Basically I have a value and I want to highlight when it occurs 5th time in a sorted column.

Thanks

 

[XLPadawan]

This will work no matter whether your data is or isn't sorted.

NOTE: Conditional Formatting rule screenshots are at the end of this post.​

Here's the formula to copy and modify for your worksheet:

=IF((COUNTIF($I23:I$24,I23))=5,TRUE,"")​