I'd like to color code a sheet based on two criteria: If the cell in the range contains specific text "A1" and if another cell, $X$7, contains an X or is not blank. I can't seem to get AND or IF or IFS to work in Sheet's conditional formatting.
Try this as a custom formula:
=if(isblank($X$7)=false,if(countif(F1:H10,"A1"),"colour me",""))="colour me"
- You said
$X$7... contains an X or is not blank. In that case, you don't need to test for "X", but just if the cell is not blank. You are looking for a value of "false".
- the test for "A1" is a simple
countif(); there may be more sophisticated methods available, but this seems to okay here.
- Open Conditional formatting,
- Select "Format cells if"
- From the dropdown, select "Custom formula is"
- Paste the formula in the cell marked "Value of formula"
- Click Done.