How do I construct a formula to check multiple values in Excel 2007?

Question

I'm trying to get Conditional Formatting in Excel 2007 to colour a cell if multiple criteria are met.

I know, that if I want to check to see if the letter p appears more than 0 times between columns G and AV in Row 21, the formula is =COUNTIF(G21:AV21,"p")>0 - But I can't make things work with multiples.

Now, I want to check a row of data. If, within that row of data the letter p appears more than 0 times, the letter b appears 0 times, f appears 0 times and a appears 0 times, I want the cell to turn green.

How do I do that?

Answer 1

Put this rule in the conditional formatting for the cell you want to change colour, set the colour in the Conditional Formatting dialog box (I have added a space after every COUNTIF so that it will show on here)





=(AND(COUNTIF (G21:AV21,"p")>0,COUNTIF (G21:AV21,"a")=0,COUNTIF (G21:AV21,"f")=0,COUNTIF (G21:AV21,"b")=0))

Answer 2

Which cell needs to be formatted as green if p, b, f, or a appear more than 0 times?



This sounds like you just want each cell that has a p, b, f, or a to be shaded green, which is simple conditional formatting. And BTW, in 2007, you can have more than 3 conditions without using formulas, but if you want a formula to test for it:



Highlight your range from G21 to AV21 and then go to conditional formatting, and use this formula:



=OR(G$21="a", G$21="b", G$21="f", G$21="p")

Answer 3

Although conditional formatting works with 3 multiple conditions you may go around this limit by creating another formula that test if multiple conditions are true eg the 4 multiple conditions which the last is false.



=AND(1=1,2>1,3>2,4<3)



All the conditions are true except 4<3 which would evaluate to false.