# 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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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)

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!

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))

Try

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

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

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?

You can use a formula in F1 like this

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

Replies
10
Views
309
Replies
1
Views
110
Replies
5
Views
170
Replies
9
Views
657
Replies
10
Views
523

1,216,109
Messages
6,128,883
Members
449,477
Latest member
panjongshing

### 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