+ Reply to Thread
Results 1 to 11 of 11

Counting conditional format cells in date range

  1. #1
    Registered User
    Join Date
    08-03-2007
    Posts
    19

    Counting conditional format cells in date range

    Hello, we have a stop light spreadsheet for status with several different projects. In each row there are conditional format status green - good, yellow - needs work, red - bad...based on data from another tab.

    There is an "overall status" column that pulls the worst color that exists for a given row.

    =IF(COUNTIF(F1:V1,"r")>0,"R",IF(COUNTIF(F1:V1,"y")>0,"Y",IF(COUNTIF(F1:V1,"g")>0,"G","")))

    There is also a date for each row. Now we want to pull the worst color in the "overall status" column for a date range...and display that on a separate summary tab.

    For 4/1/09 thru 4/15/09 - pull the worst color from the "overall status" column from that date range.

    Having some trouble with this.
    Last edited by moding blaine; 05-01-2009 at 02:52 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Counting conditional format cells in date range

    If there are multiple colours in that date range..then how do you know which to pull?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    08-03-2007
    Posts
    19

    Re: Counting conditional format cells in date range

    I thought maybe a slightly modified version of the formula that we already have? It picks the worst color for a column this time, for an associated date range. I don't know, kinda confused at this point on how to proceed.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Counting conditional format cells in date range

    Maybe you can post a sample spreadsheet showing what you would like to have.

  5. #5
    Registered User
    Join Date
    08-03-2007
    Posts
    19

    Re: Counting conditional format cells in date range

    I guess I'm looking to attach a value to each of the three colors. Green>Yellow>Red. Sorry if the request doesn't make a lot of sense. The actual file has hundreds of days.
    Attached Files Attached Files

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Counting conditional format cells in date range

    Do you mean possibly:

    =MAX(C2:N2)

    in B2, copied down... this shows the highest number which signifies worst case.. ie. 3=Red, 2=Amber,1=Green,0=None

  7. #7
    Registered User
    Join Date
    08-03-2007
    Posts
    19

    Re: Counting conditional format cells in date range

    I'm not sure if I can modify things a bit to make that work. But in our real data the conditional formatting has nothing to do with 1, 2, or 3......I just put that in as an example. In fact there different requirements for the conditional formats in some of the columns. I'm wondering if there is a way to do it specifically by color. And then some sort of lookup by date on the other tab.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Counting conditional format cells in date range

    Can you try to use more realistic (or more representative) example then... with actual conditional formats used...and what you want to show as answer in column B.

    Usually you can manipulate CF formulas within other formulas, because Excel does not count by colour, etc...

  9. #9
    Registered User
    Join Date
    08-03-2007
    Posts
    19

    Re: Counting conditional format cells in date range

    Here is a little different version with slightly different requirements that we could get by with.
    Attached Files Attached Files

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Counting conditional format cells in date range

    If you place start date in G2 and end date in H2, then use formula:

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    08-03-2007
    Posts
    19

    Re: Counting conditional format cells in date range

    This is excellent, Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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