Counting conditional format cells in date range

**moding blaine** · 04-30-2009, 02:20 PM

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.

**NBVC** · 04-30-2009, 02:29 PM

If there are multiple colours in that date range..then how do you know which to pull?

**moding blaine** · 04-30-2009, 03:11 PM

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.

**NBVC** · 04-30-2009, 03:16 PM

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

**moding blaine** · 04-30-2009, 03:45 PM

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.

**NBVC** · 04-30-2009, 03:50 PM

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

**moding blaine** · 04-30-2009, 04:01 PM

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.

**NBVC** · 04-30-2009, 04:12 PM

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

**moding blaine** · 04-30-2009, 06:27 PM

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

**NBVC** · 04-30-2009, 09:06 PM

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

Please Login or Register  to view this content.

**moding blaine** · 05-01-2009, 02:51 PM

This is excellent, Thanks!

Counting conditional format cells in date range

LinkBack

Thread Tools

Rate This Thread

Display

Counting conditional format cells in date range

Re: Counting conditional format cells in date range

Re: Counting conditional format cells in date range

Re: Counting conditional format cells in date range

Re: Counting conditional format cells in date range

Re: Counting conditional format cells in date range

Re: Counting conditional format cells in date range

Re: Counting conditional format cells in date range

Re: Counting conditional format cells in date range

Re: Counting conditional format cells in date range

Re: Counting conditional format cells in date range

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions