Google-sheets – Conditionally format a column if it contains all the values in a set

conditional formattinggoogle sheets

Each of the columns in my Google Sheet represent a farm. Each farm is a list of animals. I want to highlight the entire column if the farm has a duck, a horse, and a cow. So the column may look like this:

Duck

Horse

Cow

Potato

Octopus

And I want the entire column, including Potato and Octopus, to be highlighted. If the column did not have Horse, I would want none of them highlighted. I know I can do this with a massive list of AND and ORs, but there has to be a better way!

Edit: Research efforts: Googling any combination of these words gives me things I don't want. It tells me how to apply a conditional format to an entire column – but what it means by that is that it will highlight cells in the column that match X, not that it will conditionally highlight the entire column based on values inside that column. I've found a few things that work, but they're either really complicated (a series of AND/OR) or not really clear on why they work.

Best Answer

Example for a column with 100 itens (row 1 as header row + 100 rows for farm things such as "cow" and "potato"):

  1. At the cell A1, add this formula: =countif(A2:A101,"duck")*countif(A2:A101,"horse")*countif(A2:A101,"cow")
  2. Select the cell A1 again, then go to Format > Conditional formatting and set Apply to range A1:A101, then Format cells if... Custom formula is =A$1>0, choose the formatting that you wish (e.g. yellow Fill color)
  3. Once again select the cell A1, then go to Format > Number > More Formats > Custom number format, then type "FARM 1" (yup, use the double quotes) and click Apply.

Now add random words to cells A2, A3 et cetera (until cell A101). You'll notice that if you add duck, horse and cow at least 1 time each, the entire column will change according with the conditional formatting that you preset on step 2 above.

How it works: the formula counts how many times the word "duck" occurs in the range A2:101, then multiplies the result by the number of times that the word "horse" occurs in the same range, then multiplies the result by the number of times that the word "cow" occurs in the same range. Therefore, if at least one of these 3 words is completely absent in the range A2:A101, the counting of such word will be zero, thus causing the result of the entire multiplication to be zero. And the conditional formatting only formats the column if the result of such multiplication (at the cell A1) is bigger than zero.

If e.g. you add the word duck 5 times, then add the word horse 20 times but don't add the word cow, the formula will calculate 5 x 20 x 0 and therefore will store 0 at the cell A1.

Related Topic