Conditional Formatting Formula With Formula-driven Returned Values

G

G00DNESS

Board Regular
Joined
Aug 6, 2014
Messages
60
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) :confused:

Thanks in advance for any resolve!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Why do you have the SUM function? It serves no purpose in your equation. You only use that if you are adding a range of cells, and do not want to to write each cell individually, i.e.
=SUM(A1:A100)

Your equation should look something like this:
Code: 
=(A7-COUNTIF(C7:C9,"X"))>5
 
Last edited:
Upvote 0
Joe4 said:
Why do you have the SUM function? It serves no purpose in your equation. You only use that if you are adding a range of cells, and do not want to to write each cell individually, i.e.
=SUM(A1:A100)

Your equation should look something like this:
Code: 
=(A7-COUNTIF(C7:C9,""X"))>5
Click to expand...

I get an error message...says the formula typed contains an error.
 
Upvote 0
Whoops. Had an extra double-quote before the X. I went back and removed it.
Sorry about that.
 
Upvote 0
Joe4 said:
Whoops. Had an extra double-quote before the X. I went back and removed it.
Sorry about that.
Click to expand...

Unfortunately when I received the error message I did remove the extra ")" to see if that would correct it. No such luck.
 
Upvote 0
Unfortunately when I received the error message I did remove the extra ")" to see if that would correct it. No such luck.
Click to expand...
It sounds like you removed the wrong thing. That is a right-parend, not a double-quote.
Go back to my first reply above (post #2). As I mentioned, I fixed the formula there. So it is correct now.
 
Upvote 0
Joe4 said:
It sounds like you removed the wrong thing. That is a right-parend, not a double-quote.
Go back to my first reply above (post #2). As I mentioned, I fixed the formula there. So it is correct now.
Click to expand...

Okay! I used your corrected formula A7-COUNTIF(C7:C9,""X""))>5. The formula-driven returned value of the cell being formatted equals 6, but the cell did not turn red when I entered your formula. It should because the value is greater than 5.
 
Upvote 0
Are you sure you selected a Formatting Option (I have been known to set the formula and forget to pick the formatting option a few times myself)?
What is the value in cell A7?
If you place the following formula somewhere on your sheet, what does it return?
=ISNUMBER(A7)
 
Upvote 0
Joe4 said:
Are you sure you selected a Formatting Option (I have been known to set the formula and forget to pick the formatting option a few times myself)?
What is the value in cell A7?
If you place the following formula somewhere on your sheet, what does it return?
=ISNUMBER(A7)
Click to expand...


It returns TRUE for =ISNUMBER(A7)
Yes. I did use conditional formatting.
The value of A7 is blank. That is where I would enter a date. The value of C7 is blank. The formula for C7 is =IF(AO7<12.1,"X","")
 
Upvote 0
If A7 is blank, this statement cannot be true:
Code: 
=(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.

The value of A7 is blank. That is where I would enter a date.
Click to expand...
Are you really intending to subtract the counts from cells from C7 to C9 from a date? That sounds rather odd to me.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Locked
  • Question
Exccel - date formula that drives multiple other formulas
Replies
2
Views
111
Fluff
Fluff
V
  • Solved
Conditional Formatting Formula
Replies
2
Views
482
awoohaw
awoohaw
SaraWitch
  • Solved
Return values based on multiple cells
Replies
4
Views
227
etaf
etaf
L
  • Solved
Conditional Formatting of a formula based date
Replies
8
Views
211
Alex Blakenburg
A
Y
  • Solved
Variable decimal format: displayed value differs from the value used when referenced in another formula
Replies
4
Views
280
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,585
Messages
6,120,394
Members
448,957
Latest member
Hat4Life

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top