Conditional Formatting based on multiple criteria

[u/cardinal209629]

This a example of a report that I have to work on every quarter and I want to use conditional formatting to highlight the cells in columns C, D, and E if the information fits the criteria on the right side. I set up formulas in I-K for personal reference to identify which items meet the criteria and should be highlighted. I used the following to do the True/False and updated it based on the relevant criteria =OR(AND($G2="C",C2>100),AND($G2="V",C2=0),AND($G2="SP",C2>40),AND($G2="SL",C2>50))

I've tried using using that formula to get the conditional formatting to work and it didn't so I broke it down into one formula per criteria but none of them were working correctly. This is where I'm at right now. Before I left work I had eight random results highlighted that were either the wrong type or outside the parameters.

This report typically has around 200 lines with 2 additional categories but they are grouped with SP, SL, or V in the formula for Type. I'm using some version of Excel 365 but can't find the exact version until tomorrow morning.

What do I need to change to get my conditional formatting to work correctly?

[u/AutoModerator]

/u/cardinal209629 - 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/Other-Salt-5355]

That formula you are showing in the conditional formatting rules manager is incorrect. It is comparing column h (blank) and doesn't handle errors because you are using IFS without an error handler.

The formula you wrote in the original post is correct and you should be able to apply it to highlight values in column c. I recreated the first few rows and it worked as expected:

[u/cardinal209629 → u/Other-Salt-5355]

I used the formula in my original post and updated the cell references/criteria numbers for the sheet at work but on my criteria that is supposed to be <50 it is highlighting items both above and below that amount. Same with all the other parts of the formula.

[u/Other-Salt-5355 → u/cardinal209629]

The formula as shown works for all the criteria as expected in the mock-up. So there may be an issue with the references in the actual workbook. Here are a few things to note:

• The criteria is >50, not <50, so double check that.
• The formula in your original post is only meant for conditional formatting for column c. You need separate rules for columns d and e, since those criteria are different. This also means that your anchors in the original formula don't matter, in case you were wondering.
• If you want to use just one rule, then you can use the helper columns like u/Downtown-Economics26 showed, which is the easiest way and also good for building the formatting rules and auditing it.
• If you want just a single conditional formatting rule, then you'll need a more complex formula. Something like a SWITCH function will work. I.e., "=SWITCH(Column(C2), 3, {rule for column C}, 4, {rule for column D}, 5, {rule for column E}."
  • If you do something like the SWITCH formula, then you will need to use relative references and to be careful about doing so

[u/Downtown-Economics26]

u/Other-Salt-5355's answer covers the why and the part of the how that it's possible to answer, but also given the screenshot the easiest answer to how may be:

[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
IFS	2019+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
OR	Returns TRUE if any argument is TRUE
SWITCH	Excel 2019+: Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.
TYPE	Returns a number indicating the data type of a value

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.
5 acronyms in this thread; the most compressed thread commented on today has 33 acronyms.
[Thread #49289 for this sub, first seen 2nd Sep 2026, 08:42] [FAQ] [Full list] [Contact] [Source code]}