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

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.
Similar Threads
  1. alabil

    Finding specific value

    alabil, Mar 7, 2012, in forum: Excel
    Replies:
    0
    Views:
    273
    alabil
    Mar 7, 2012
  2. palombo
    Replies:
    0
    Views:
    364
    palombo
    Jul 2, 2012
  3. Siegfried

    sum based on value in a range

    Siegfried, Jun 27, 2014, in forum: Excel
    Replies:
    0
    Views:
    615
    Siegfried
    Jun 27, 2014
  4. DanM
    Replies:
    1
    Views:
    383
    XLPadawan
    Jun 13, 2016
  5. hyukfgh
    Replies:
    2
    Views:
    623
    hyukfgh
    Oct 16, 2014
Loading...