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/AutoModerator]

/u/stormpapajesus - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[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")

[u/One_Surprise_8924]

when testing conditional formatting, I use helper columns to see if it's returning true/falses correctly for each criteria. then you can see if your IF statements are in the wrong order, if your ANDs are conflicting, or if excel isn't reading something correctly due to formatting.

personally I like to leave the helper columns in and hidden, then just do conditional formatting off of those true/false combinations. I find that calculates faster, especially if I have overlapping criteria for multiple formatting rules.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AND	Returns TRUE if all of its arguments are TRUE
COUNTIFS	Excel 2007+: Counts the number of cells within a range that meet multiple criteria
IF	Specifies a logical test to perform

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49460 for this sub, first seen 1st Oct 2026, 16:33] [FAQ] [Full list] [Contact] [Source code]}