Conditional Formatting Formula With Formula-driven Returned Values

[G00DNESS]

Using Excel 2003, I am trying to use a conditional formatting formula so that if the (formula-driven returned value) in Cell A7 minus the count of formula-driven cells in Range C7:C9 that return "X" is greater than 5 the font color will equal red. I used this formula that does not work: =SUM(A7-COUNTIF(C7:C9,"X")>5)

Thanks in advance for any resolve!

 

[G00DNESS]

If A7 is blank, this statement cannot be true:

=(A7-COUNTIF(C7:C9,"X"))>5

as zero minus anything is less than 5, so your Conditional Formatting Criteria will be false and not apply your Conditional Formatting.


Are you really intending to subtract the counts from cells from C7 to C9 from a date? That sounds rather odd to me.

My apologies! I did that wrong. A7 is not blank. The returned value is 6, and the formula within it is =COUNTA(B7:B9)

 

[Joe4]

So, if you enter this formula somewhere on your sheet for testing purposes, what does it return?

=(A7-COUNTIF(C7:C9,"X"))

 

[G00DNESS]

So, if you enter this formula somewhere on your sheet for testing purposes, what does it return?

=(A7-COUNTIF(C7:C9,"X"))

It returns the value of 6

 

[G00DNESS]

It returns the value of 6

That sheet is screwed up. I'm using a corrected sheet now to handle the X by row. It is now A27 and C27:C50

 

[G00DNESS]

That sheet is screwed up. I'm using a corrected sheet now to handle the X by row. It is now A27 and C27:C50

So to correct this, I am trying to use a conditional formatting formula so that if the (formula-driven returned value) in Cell A27 minus the count of formula-driven cells in Range C27:C50 that return "X" is greater than 5 the font color will equal red.

 

[G00DNESS]

If A7 is blank, this statement cannot be true:

=(A7-COUNTIF(C7:C9,"X"))>5

as zero minus anything is less than 5, so your Conditional Formatting Criteria will be false and not apply your Conditional Formatting.


Are you really intending to subtract the counts from cells from C7 to C9 from a date? That sounds rather odd to me.

I am intending to subtract the counts from cells C27:C50 from the number of dates that are returned in A27. I had to make a new sheet because the first one sucked. I am actually trying to use a conditional formatting formula for Cell A30 so that if the (formula-driven returned value) in Cell A27 minus the count of the formula-driven cells in Range C27:C50 that return "X" is greater than 5 the font color will equal red. This is so frustrating for me.

 

[Joe4]

One of the best ways of debugging is to actually test out your formulas to see what they return before using them in your conditional formatting formula.
So what does the following formula return?
=A27-COUNTIF(C27:C50)