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

Discussion in 'Excel' started by Shivesh, Oct 13, 2015.

  1. Shivesh

    Shivesh

    Joined:
    Oct 13, 2015
    Messages:
    1
    Likes Received:
    0
    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
     
    Shivesh, Oct 13, 2015
    #1
    1. Advertisements

  2. Shivesh

    XLPadawan

    Joined:
    Jun 9, 2016
    Messages:
    32
    Likes Received:
    5
    Location:
    Allen, TX
    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,"")​

    upload_2016-6-14_11-13-42.png

    upload_2016-6-14_11-20-58.png
    upload_2016-6-14_11-19-47.png
     
    XLPadawan, Jun 14, 2016
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.