0

What I'm trying to do is to highlight rows L to P in red if all cells say "False", and here is the formula I used:

=(sum(arrayformula(n(regexmatch($L2:$P2, "False")))) = 5)

This does not work unfortunately. I also tried the following formula (another way to put it) without any luck either:

=(sum(arrayformula(n(regexmatch($L2:$P2, "True|Unsure")))) = 0)

Next, is a snapshot of the conditional format rule:

enter image description here

Please help me figure out why the above formulas aren't working. If you need to see the sheet I'm working on, I've made a copy here.

Improve this question
2
  • 1
    I just changed the share option and should no longer ask for permission request. Apologies for this.
    – Vee
    Commented Sep 20, 2016 at 12:43
  • Please consider to do your tests on another copy instead of the one linked to the question. The current version has no/yes instead of false/true.
    – Rubén - Volunteer Moderator - ♦
    Commented Sep 20, 2016 at 13:21

1 Answer 1

Reset to default
1

Short answer

  1. FALSE and TRUE are Google Sheets keywords, they represent the respective boolean values. To use them as text, use ' as prefix, In the regular expression change False to FALSE.
  2. The "Apply to range" start cell should be L2 instead of L1.

Explanation

Apply to range

The relative references in custom formatting formulas takes the start cell as the pivot. As the values FALSE/TRUE/Unsure start on row 2, on Apply to range instead of using L1 as the start cell, use L2.

Custom formula

The formula

=(sum(arrayformula(n(regexmatch($L2:$P2, "False")))) = 5)

returns #VALUE! and the following error description:

Error
Function REGEXMATCH parameter 1 expects text values. But 'FALSE' is a boolean and cannot be coerced to a text.

Note: To see the above error message in a Google Sheets spreadsheet, add the above formula to any cell not in the columns L-P

As REGEX is case sensitive the formula to use is:

 =(sum(arrayformula(n(regexmatch($L2:$P2, "FALSE")))) = 5)

An alternative formula to avoid the use of prefix is the following

 =ARRAYFORMULA(SUM(IFERROR(IF($L2:$P2,1,0),1))=0)
Improve this answer
9
  • Thanks for the input @Ruben. I did try the suggested formulas but they didn't work. In the spreadsheet, rows 2, 3 and 11 should be highlighted in red but using the formulas gave different output. I've also considered replacing true or false with yes and no, respectively just to make it simpler but still no luck. If you could check the spreadsheet, I've created sheets for formula 1, 2 and 3 (with yes and no instead of true or false) so you can see how the spreadsheet interpreted the different. formulas. Thanks.
    – Vee
    Commented Sep 20, 2016 at 4:00
  • I made copy of your file yesterday. The formulas work fine on that copy on that copy. Today, the link on the question requires to request permission to access it.
    – Rubén - Volunteer Moderator - ♦
    Commented Sep 20, 2016 at 10:33
  • I think that I figured out what it the problem and updated the answer accordingly.
    – Rubén - Volunteer Moderator - ♦
    Commented Sep 20, 2016 at 11:05
  • The custom formula should only be applied to columns L to P and not the entire row.
    – Vee
    Commented Sep 20, 2016 at 13:02
  • The question title confused me. I rolled back the answer and updated the question title.
    – Rubén - Volunteer Moderator - ♦
    Commented Sep 20, 2016 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.