Excel Magic Trick 1400: Conditionally Format Row in Class Enrollment Table with Complex Criteria

Download Files:

See how to Conditionally Format the row in an Enrollment Table when the following conditions are met:
1. Enrollment less than or equal to 15, then format row
2. Name Column can NOT contain: 'Introduction'
3. Department can NOT be: NURS OR MTH
See the functions: AND, SEARCH, MATCH, and ISERROR in a complex Logical Formula.

[LeilaGharani]

Great step by step approach! Love how you break it down. Thank you for the Friday CF fun!

[deanbailiff]

Hello Mike, A great solution especially using MATCH if there are a lot of criteria to check.  With just a few criteria we could use COUNTIFS:  =COUNTIFS($F4, "<="&$H$4, $D4, "<>"&$H$8&"*", $E4, "<>"&$H$11, $E4, "<>"&$H$12).

Or with critera shown to see it easier: =COUNTIFS($F4, "<=15", $D4, "<>Introduction"&"*", $E4, "<>NURS", $E4, "<>MTH")

[Eyadsgaming]

Hello Mr .Mike,

I found an issue in 5:00 the "SEARCH" equation haven't calculated the word "INTRODUCTION" in "D14", but calculated the word "INTERMEDIATE" in "D13"...
Is that because the place of the calculation in "H16" is [10R*6C]
while the table in "A3" is [11R*6C]?

[pmsocho]

Great step by step explanation as always!

[raheemghalib]

You are awesome dude!!! I like the way you breakdown the formulas for explanation. Cheers! Keep up the good work.

[krn]

Brilliant. If I lived near Seattle I would take your class. :) Great job as usual Mike.

[ganesh]

I have data in row column, and my some number is like this 7098,
7099, 7091, 7010 my number started from 70 in row, I want that formula if any one start number other than 70, so I can see there na, like if some one start number 90, or 80, in that which formula Should I use, if there is 70 then ok other wise na

[zaighamuddinfarooqui]

"Funtastic". You are really master of formula base tricks of Excel. 👌