Google-Sheets Conditional Formatting based on multiple conditions

Question

I am trying to format a cell based on multiple conditions. I am creating a spreadsheet to keep track of items borrowed. Let's say I am lending books. I want to have a list of books, one name in each cell. Then below that I want to have 3 columns: One column to enter the name of the book borrowed, the borrowing date, and the return date. I want to turn the cell with the book name RED, if the book has been borrowed AND if the return date is BLANK, meaning book is out. In my example screenshot, cell A2, and B2 should be red.

The conditional formula I have come up with is =AND($A6=A2, $C6="") for Book1 conditions, but it only works if C6 if empty, not if C8 is empty or other cells in column C where Book1 is found AND the return date is blank. There is no specific deadline to return items, just that if book has been borrowed and the return date in the same row is empty then the book name at the top should turn red.

Answer 1

Your question title asks about "multiple conditions", but very specifically you're looking to match based on any row that itself matches multiple conditions. That goes beyond the common AND operator and into a function that can process a range. You also need to be prepared for a book to be checked out and returned many times, which means there's no single row that manages the status of a given book; VLOOKUP and INDEX/MATCH are off the table too. Instead, you're effectively looking to generate a list of 0 or 1 values that match whether that book was checked out without being returned, and then coloring the cell based on whether there are any rows that match that condition.

To operate on multiple values at a time, you can use ARRAYFORMULA and then combine the output array with OR. However, one of the tricks about ARRAYFORMULA is that, to preserve the invariant about making single-value functions into array-valued functions, you can't use functions that can take arrays. This means that AND and ISBLANK don't work the way you'd like them to, but you can resolve that by using * instead of AND and = "" for ISBLANK.

One such solution (working example):

=OR(ARRAYFORMULA((A1 = $A$5:$A) * ($C$5:$C = "")))

ARRAYFORMULA isn't the only function to operate on a list of values, though; you could also use FILTER directly to only return matching rows. Here, you're checking whether any row has a matching book name and a blank return value, and then confirming that the value is not the #N/A that FILTER returns when nothing matches.

One such solution (working example):

=NOT(ISNA(FILTER($A$8:$C, $A$8:$A = A1, $C$8:$C = "")))

Of course, you can also take advantage of the fact that you're only checking blanks to use tehhowch's solution with COUNTA and FILTER above. However, since that solution won't work for arbitrary expressions, you can use ARRAYFORMULA or FILTER if your needs become more complex.

Answer 2

Compare the result of COUNTA applied to the in and out ranges.

E.g. COUNTA(FILTER($B6:$B,$A6:$A=A2)) will count how many times a specific book is checked out, while COUNTA(FILTER($C6:$C, $A6:$A=A2)) will count how many times it is checked back in