Ask Your Question

Revision history [back]

You need to make several corrections.

  1. You need to prepare your sample carefully. E.g., pay attention to the ranges you mention in the explanations: looks like you need to check range A4:A26, but both explanations an existing conditional formatting mention A2:A24.
  2. You need to use formula like


because conditional formatting uses usual rules for evaluation of references, including rules for relative vs absolute references, and rules for using a cell from a rang found by intersection. Specifically, you used


in the range A1:A3. That meant that you used usual formula with equality check (simple =, not an array formula), with relative references (well, you used $ before column name, but only in one case; and not before row numbers), which translates to "find (by intersection) a cell in range one cell below till 25 cells below; check if that single cell is equal to "Yes"."

Note that COUNTIF uses case-insensitive check, so that using "Yes", "YES", or "yEs" will all do.

And finally, you would need to define the range for your conditional formatting to be A1;A3- not A1:A3 (note using semicolon instead of colon) - because you need to exclude A2 from the conditional format.