Google-sheets – Conditional Formatting IF/AND Google Sheets

conditional formattinggoogle sheets

EDIT 2:
Test document. See comment of what I'm trying to accomplish in cell A1.
https://docs.google.com/spreadsheets/d/1lqrPbVqSrO257PqOYKLuCTxG1iiLaL6KYawp1hu4n5c/edit#gid=0

EDIT:
I've tried this code and it doesn't operate correctly. It highlights some cells, but not all within the range, and doesn't operate the duplicate identification correctly.

=if(and($I5=false), countifs($L:L, L1)>1)

Original post:

I am trying to use conditional formatting to highlight a row when the following conditions are met:

The value of cell $I1="FALSE" (i.e. the box in the cell is unchecked)

AND

The value in $L1 has at least one duplicate value in column L.

I am currently using the following formula to only highlight the duplicate values, but cannot get it to function along with the secondary condition of $I1=FALSE.

For the range K:L (It doesn't allow me to highlight the whole row for some reason)

=countif($L:L,L1)>1

I also have an additional formatting rule in place that is working as I want it to:

=$I1=TRUE

This is highlighting the row a different color once the checkbox in column I is checked.

Best Answer

I'm unable to access your spreadsheet, but according with your report the conditional formatting formula that you're looking for is this:

=AND(EQ($I$1,FALSE),EQ(countif($L:L,$L$1)>1,TRUE))

Related Solutions

Google-sheets – Conditional formatting with multiple criteria for a range of cells

Writing a formula that satisfies your criteria is a matter of breaking down what your criteria are and implementing corresponding Sheets functions.

You have established that the formatting should be applied only if a cell is not empty. The EQ function tests whether or not one value (such as a referenced cell's) is the same as another. Since we want to test against an empty cell, we will use "" (the empty string) in our EQ function. So EQ(A1,""). But you want it to return TRUE if the cell is NOT empty, so we will enclose this expression within the NOT function.

NOT(EQ(A1,""))

You also established that the formatting should be applied if the cell's value is not -------. Once again, we can use EQ for this. EQ(A1,"-------"). And again, we'll wrap it in the NOT function to meet your criterium.

NOT(EQ(A1,"-------"))

Finally, you want BOTH of these criteria to be met if the formatting is to be applied. So we'll use the AND function, inputting the two formulae we put together above as the arguments. Your final formula is:

=AND(NOT(EQ(A1,"")),NOT(EQ(A1,"-------")))

Make sure that, when you are creating your conditional formatting rule, you set the condition field to "Custom formula is," or it won't work.

ADDENDUM: Normal Human has offered an alternate formula that is both shorter and easier on the eyes than mine. It utilises logical operators in place of some of Sheets' logical functions and so is not quite as easy to follow without knowledge of these operators. The logic is exactly the same, however. (<> is the operator for "not equal to.")

=AND(A1<>"", A1<>"-------")

Google-sheets – Conditional Formatting Based on Date Formula

You're on the right track, just get rid of If: the custom formula should be

=And((E2+7)>Today(), H2="No")

Explanation

If requires at least two arguments: condition and the result to be returned. Here you just have a condition, so if is neither suitable nor needed. The formula used for conditional formatting should return a boolean value (True/False), or a numeric value, which will be coerced to boolean as follows: 0 will be interpreted as False, nonzero as True. The formatting is applied if the formula returns True.

In your case, and returns a boolean value, which is all you need.

Best Answer

Related Solutions

Google-sheets – Conditional formatting with multiple criteria for a range of cells

Google-sheets – Conditional Formatting Based on Date Formula

Explanation

Related Topic