+ Reply to Thread
Results 1 to 5 of 5

How To Count Conditionally Formatted Cells

  1. #1
    Forum Contributor
    Join Date
    08-04-2010
    Location
    Adelaide, Australia
    MS-Off Ver
    Office 365
    Posts
    176

    How To Count Conditionally Formatted Cells

    Hi all,

    thanks to Martin at this forum, I have some CF that will shade cells when an adjacent cell is of the same value - as a followup to that, I then tried looking at ways to count the maximum number of contiguously shaded cells for each row.

    A quick look online told me that counting colors is notoriously unreliable however, I found several UDF's to try - alas, none of them returned the results I was hoping for.

    I then thought that perhaps I could take the CF formula that Martin provided to produce the shading in the first place and wrap that in another formula somehow to count up what I wanted but alas, I'm again having no luck. I've tried some variations of IF, COUNT, COUNTIF and COUNTIFS but I don't know if I'm getting the syntax wrong or if it's just not feasible.

    I'd be grateful if someone would be willing to take a look for me - the attached workbook has some cells highlighted in pink to show what I'm hoping for...

    Cheers,

    AJ
    Attached Files Attached Files
    Last edited by ScotyB; 11-16-2013 at 07:49 PM. Reason: to mark as solved
    Always grateful for the help here - thanks.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,672

    Re: How To Count Conditionally Formatted Cells

    In Z2, =MAX(FREQUENCY(IF($B2:$X2 = Z$1, COLUMN($B2:$X2)), IF($B2:$X2 <> Z$1, COLUMN($B2:$X2))))

    The formula MUST be confirmed with Ctrl+Shift+Enter instead of just Enter.

    Then copy across and down.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How To Count Conditionally Formatted Cells

    hmm i notice you didn't note the amendment i made to the last column as single 0 will change colour otherwise
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Contributor
    Join Date
    08-04-2010
    Location
    Adelaide, Australia
    MS-Off Ver
    Office 365
    Posts
    176

    Re: How To Count Conditionally Formatted Cells

    shg - thank you; that works a treat and I hadn't even considered using FREQUENCY (learn something new every day, lol)

    Martin - t'was noticed :-) - my master workbook has all your CF in it (many thanks again) - I think I got lost in that last dummy workbook with experimentation and changed lots of things around to try and get things to work.

    Cheers, guys, my workbook is now functioning exactly as I had hoped for - kudos to you both for your help.

    AJ

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,672

    Re: How To Count Conditionally Formatted Cells

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. UDF to Count Conditionally formatted cells by colour
    By ozzy_q in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 10-06-2013, 09:00 AM
  2. [SOLVED] count conditionally formatted cells
    By littlefoot in forum Excel General
    Replies: 5
    Last Post: 07-12-2012, 08:40 AM
  3. How to count cell which are conditionally formatted?
    By parekhharsh_j in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-12-2011, 02:53 PM
  4. Replies: 1
    Last Post: 08-26-2011, 09:57 AM
  5. [SOLVED] Can I count conditionally formatted red cells in Excel 2000
    By ExcelUser in forum Excel General
    Replies: 1
    Last Post: 08-02-2006, 10:45 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1