Conditional Formatting with COUNTIF

R

Rovermog

I would like to have cell A11 set to show red via conditional formatting if
the cells F11:J11 and AI11:BF11 have either a number, date or text in them,
but excluding cells that contain X and the total is less than 12.
This is the formulae I have been trying;
=(COUNTIF(F11:J11,"<>"&X)+COUNTIF(AI11:BF11,"<>"&X))<12

Similarly A11 should go yellow if the total is between 12 and 25.

Any suggestions.
 
S

Stefi

Try this:
first condition for red
=COUNTA(F11:J11,AI11:BF11)-COUNTIF(F11:J11,"<>"&"X")-COUNTIF(AI11:BF11,"<>"&"X")<12
second condition for yellow
=COUNTA(F11:J11,AI11:BF11)-COUNTIF(F11:J11,"<>"&"X")-COUNTIF(AI11:BF11,"<>"&"X")<25


Regards,
Stefi


„Rovermog†ezt írta:
 
B

Bob Phillips

=AND(COUNTIF(F11:J11,"<>X")+COUNTIF(AI11:BF11,"<>X")-COUNTBLANK(F11:J11)-COUNTBLANK(AI11:BF11)>=12,
COUNTIF(F11:J11,"<>X")+COUNTIF(AI11:BF11,"<>X")-COUNTBLANK(F11:J11)-COUNTBLANK(AI11:BF11)<=15)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If with countif or counta 3
If and Countif 2
Excel Countif Function - Calculating Between Two Sets of Values 3
Conditional formatting dates 3
conditional formatting update issue 5
Conditional Formatting to Another Cell 0
conditional formatting - too many arguments? 3
Conditional Formatting with VBA 4

Top