# IF Functions in conditional formats

#### marello

##### New Member
I don't really understand IF functions (or Excel) in any great depth! At the moment I'm trying something that I think is a bit above my head and I'd love it if someone could help me and explain why you do what you do.

I have 3 colums (A,B+C), each with about 1,500 values in ranging from 0 to 11. the 5th colum in the row display a value based on a formula. I'm trying to do a conditional format so if two of the first colums have a 0 in then the answer is a certain colour, if there's only one another, and if there aren't any zero's a final colour.

So i'm trying to put an IF formula into my conditional format box.

The formula for colum number 5 is:
=SUMPRODUCT(\$A\$1:\$C\$1,A205:C205)

So I think I want to write something along the lines of:

=IF(\$A=0\$B=0, and then have my true value set to the colour, say blue.

But how do I write this properly, I really dont understand!

Also as a side note, if I had an IF function, what would i need to write in the true value space to get the cell just to display the result of a formula?

Thank you very much for your help.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### NBVC

##### Well-known Member
With Conditional Ifs, you don't actually need to place an IF; it is understand by the nature of the function.

So you would insert the formula =And(A1=0,B1=0) and choose Format

for either condition, use =OR(A1=0,B1=0)

and for none, =AND(A1<>0,B1<>0)

#### marello

##### New Member
Thank you NBVC each of those formulas is what I'm after.

However if you're only allowed 3 conditional formats per colum I need to condense them down? The conditional format is based on 3 colums, so for example

=OR(A1=0,B1=0) works great, but I also have to put in =OR(B1=0,C1=0) and =OR(A1=0,C1=0), is there any way of combining the 3 so that I could have one conditional format to say "if any two colums contain zeros colour the answer blue" so that I could then go on to say "i only one colum has a zero make it green" and then "if none of them have a zero make it red"?

Thanks!

#### NBVC

##### Well-known Member
I think this is what you are after, then:

=Or(And(A1=0,B1=0),and(B1=0,C1=0),and(A1=0,C1=0))

#### barry houdini

##### MrExcel MVP

Try

=COUNTIF(A1:C1,0)=2 for blue

=COUNTIF(A1:C1,0)=1 for green etc.....

#### marello

##### New Member
Out of curiosity how would I write a formula that says:

if there are two zeros in colums A to C copy the value in colum E into a new cell?

#### barry houdini

##### MrExcel MVP
You can use a formula in F1 like this

=IF(COUNTIF(A1:C1,0)=2,E1,"")

Replies
2
Views
37
Replies
10
Views
109
Replies
6
Views
176
Replies
7
Views
164
Replies
2
Views
239