Counting conditional format cells in date range

1. 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.

2. 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?

3. 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. Re: Counting conditional format cells in date range

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

5. 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.

6. 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. 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. 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. Re: Counting conditional format cells in date range

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

10. 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. Re: Counting conditional format cells in date range

This is excellent, Thanks!

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

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