r/excel•
ngiori

Conditional Formatting Formula to Format Cells that are NOT 0 or 1

solved

Hey guys, thanks for reading.

I'm trying to format a SUMPRODUCT table so that if anyone using the sheet after me enters anything but a 1 or 0 (which will make the SUMPRODUCT go wonky), it will let the person know with some bright red formatting. This table is to group bills associated to certain customers quickly and easily (as there are <20 possible).

The range I'll be using is D3:K15

So far, I've tried using "Format only cells that contain.." and put in the formulas

=OR(1,0) - for range d3:k15

=OR(d3=1,d3=0) - for range d3:k15

=OR(d3:k15=1, d3:k15=0) - for range d3:k15

and I've also tried "Use a formula to determine which cells to format.." using formulas

=OR(d3=1, d3=0) - for range d3:k15

I think you understand what I want. Where am I going wrong?

Thanks in advance!

Posted Aug 12, 2015, 3:24 PM UTC · edited

19
Loading comments…