# 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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### 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,"") Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,163,995
Messages
5,834,777
Members
430,321
Latest member
yemisimi11 ### 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.

### Which adblocker are you using?    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

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