Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Minneapolis, Minnesota, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting text values for conditional formatting (2007)

    I need to perform conditional formatting based a count of text values. Essentially, if "Eric" appears in a column more than 6 times, I need to set one conditional format. If "Eric" appears 5 times, I need to set a second conditional format. If "Eric" appears 4 or fewer times, I need to set a third conditional value. I need to evaluate this for a list of 6 names. Is this even possible? Would it be better if I only had two different conditional formats?

    Currently, I'm leaning towards using data validation to ensure that the entries are exact. From there, I'm assuming a long and complicated IF using COUNTIF within the conditional formatting dialog. I'll certainly accept being told this is insane, but it would really make my life easier.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting text values for conditional formatting (2007)

    A cell can have a maximum of 3 different conditional formats. Together with the default format, that makes 4 different formats. So you can do what you want for a single name, but not for six names if you really want to do this in a single cell - you'd end up with 3^6 = 729 different formats (which makes no sense anyway), unless I have misunderstood what you want.

    For a single name it would work as follows. Let's say you want to look at the number of times "Eric" occurs in column A.
    Set the formatting of the cell the way you want it to be if "Eric" occurs 4 times or less.
    Then select Format | Conditional Formatting...
    Select "Formula Is" from the first dropdown list.
    Enter the formula =COUNTIF(A:A,"Eric")=5 in the box next to it.
    Click Format...
    Specify the formatting you want, then click OK to return to the Conditional Formatting dialog.
    Click Add>>.
    Select "Formula Is" from the first dropdown list.
    Enter the formula =COUNTIF(A:A,"Eric")>5 in the box next to it.
    Click Format...
    Specify the formatting you want, then click OK to return to the Conditional Formatting dialog.
    Finally, click OK to close the Conditional Formatting dialog.

    If you really need more than 4 different formats, you need to use VBA code.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Counting text values for conditional formatting (2007)

    Do you want different formats for each name, or do you want one format for any of the names appearing 6 times, one for 5 times and one for 4 or less?
    If you are using Excel 2007 you are not limited to 3 rules, but I agree with Hans that that many formats would be too much - I don't think it would make things any clearer for you!
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    New Lounger
    Join Date
    Sep 2002
    Location
    Minneapolis, Minnesota, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting text values for conditional formatting (2007)

    In my ideal world, I'd like to set this up as follows:
    1) If the name in the cell appears in that column more than six times, format it red.
    2) If the name in the cell appears in that column 6 times, format it yellow.
    3) If the name in the cell appears in that column less than 6 times, format it green.

    Based on the replies from both Rory and Hans, I'm wondering if I could do it by using the actual value of the cell?

    Thank you both for your help!
    DeNae

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting text values for conditional formatting (2007)

    See the attached example. I set the background color for D13 to green (the default).
    Conditional formatting changes the background color depending on the number of occurrences of the name in column A. The conditional formatting uses formulas involving COUNTIF.
    Attached Files Attached Files

  6. #6
    New Lounger
    Join Date
    Sep 2002
    Location
    Minneapolis, Minnesota, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting text values for conditional formatting (2007)

    Hans:

    That is exactly what I was looking for! Thank you so much!

    DeNae

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •