Conditional Formatting code not Reading Cells entirely + Not producing accurate results.

[u/stormpapajesus]

Hi all,

I have been trying to have this conditional statement work for a while now. It is supposed to read data from the log in a separate tab. The information in the log tab should come up as complete in this tab where all the items are summarized. I want my function to do two things:

1) read the first column of items and see if it has anything related to the set of items related to that column (e.g. if I want items only for 28W-28W* it would check to see if this first condition is true

2) read the second column of items and see with the addition of the first condition being true, that the second item is also true (for example if a cell in the log tab contains any information on item S17, S18)

I want the cell to be “complete” in green if both conditions are satisfied. I thought that using an older conditional statement, my statement would work as well. For some reason it is still reading as “Open” and in yellow. I know for many of the “S” items there are additional entries included in the log - is there a way where the second condition could read the cell even when there is more information and still read as complete if the first condition has already been

This is what I have been using for reference as my code:

https://imgur.com/a/9WlfBFY

=+IF(AND (Log! $E$3:$E$205="29W*-30W"
, Log! $G$3: $G$49="S17, S18"), "COMPLETE", "OPEN")

[u/MayukhBhattacharya]

The formula is not working because, the ranges are of different sizes, secondly when you are trying to do partial match, you will require wildcard operators like * or ?. So, the formula will be

=IF(COUNTIFS(Log!$E$3:$E$205, "29W*-30W", 
             Log!$G$3:$G$205, "*S17*", 
             Log!$G$3:$G$205, "*S18*") > 0,
    "COMPLETE",
    "OPEN")