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?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. alabil

    Finding specific value

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

    sum based on value in a range

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