CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Warren Meroney
Warren Meroney

Reputation: 39

Formula to Test for Blanks in two Columns Against a Third Column

So I am using a conditional formatting custom formula to highlight a cell if the column it is summing from another spreadsheet has blanks, but I don't know how to do it with changing ranges. Basically what I want to do is use a third column, say column A, to determine the length of the range (of rows) I want to scan with CountBlank, and if it picks up a blank in there to return a "True".

So basically:

Column A     Column B      Column C      Column D
Person 1       5:30AM       3:00PM         9.5 
Person 2       5:00AM                       8
Person 3       4:30AM       4:00PM        10.5

So ideally, the cell sums the fourth column with a different function (already have that), and it conditionally formats itself if a blank is picked up in Columns B or C, going all the way down to the last row of column A that has a value. Any help here would be appreciated, thank you.

Upvotes: 1

Views: 114

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34230

If I understand you correctly, the custom formula for conditional formatting should be:

=or(countifs(A2:A,"<>",B2:B,""),countifs(A2:A,"<>",C2:C,""))

enter image description here

Suppose you had a list of people's names starting in F2 and their total hours starting in G2. Then you could alter the formula to:

=or(countifs(A$2:A,F2,B$2:B,""),countifs(A$2:A,F2,C$2:C,""))

enter image description here

Upvotes: 1

Related Questions