Excel Advanced If Functions

If you need to do something precise when two or more circumstances are TRUE, you can use the IF function in grouping with the AND function to calculate conditions with a test, then take one action if the result is CORRECT, and (optionally) do something otherwise if the outcome of the test is FALSE.

If color = green AND size = small

In the case shown below, we just want to “mark” or “flag” records where the color is green AND the size is small. In added words, we need to check cells in column B for the color “green” AND check cells in column C to see if the size is “small”. Then, if both conditions are TRUE, we can take a precise action.

In D6, the formula was using is this: =IF(AND(B6=”red”,C6=”small”),”x”,””)

If function with AND & OR

In the above example shown, we simply show the status to “Yes” rows where the color is “green AND size is either “small” or “large”. To return TRUE when items are green and small or large we can use a logical statement constructed with the AND function:

In the case shown, the formula in D6 is:


You will get below Result


Please Share