arraysif-statementgoogle-sheetsconditional-formattingnonblank

Google Sheets: Conditional Formatting - color all non-empty cells in a row within a range based on the value of another cell?


 Hello!
 So I have different (non-numeric) values in Column B (B3:B). And I have transactions (formatted as currency) in Columns C through E (range C3:E). So I want every cell in any given row within that range to be colored, whenever that cell has some value (non-empty) AND the corresponding cell (of the same row) in Column B has "$" value in it.
 In other words I need a formula that would check the whole (C3:E) range, find all non-empty cells (assuming that there will be blank cells in that range), check the corresponding (of the same row number) cells in Column B and, if those corresponding cells contain "$" value, color all the non-empty cells in that row (even if there are several non-empty cells in there) within the (C3:E) range.
Let's say we see that in row 7 there are two cells (and they are within our C3:E range) that have values 23.54 and 67.90 (non-empty). That's the first condition. And we also see that the value of the corresponding cell in row 7 of Column B (B7) is "$". That's the second condition. So only cells C7 and E7 (not the whole C7-E7 row) must be colored by conditional formatting. So in the table I provided only cells D4, C7 and E7 must be colored.

 That's the custom formula for Conditional Formatting that I came up with =AND(NOT(ISBLANK(C3:E)),AND($B3="$")) but the problem is that it colors the whole row instead of the non-empty cells in that row only.
 Where am I making a mistake?

Please advise.

     Column B Column C Column D Column E    
3      None               14.50                                    12.00
4         $                                         45.90
5      Some               23.90
6       Few
7        $                    23.54                                   67.90


P.S: What would the formula be to color the cells that contain anything else but "$" with another color?


Solution

  • try like this:

    =($B3="$")*(C3<>"")
    

    enter image description here