I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.
I’ve been doing work with quite a few clients with Jet Reports recently and one of the common requirements it to hide a row when values are blank. While the clients I am working with are experienced with Excel, they often are very good with VLOOKUP and other functions, but not so much with AND/OR operators.
This can be done using an IF statement combined with an AND operator. In Excel the and statement format is different to programming/scripting languages.
The below is an example AND checking the value in cell A2 and B2 and returning true if they are both 1 otherwise returning false:
=IF(AND(A2=1,B2=1),"True","False")
Just for completeness, the below is an example OR checking the value in cell A2 and B2 and returning true if they either cell is 1 otherwise returning false:
=IF(OR(A2=1,B2=1),"True","False")