Excel Formula for IF with multiple conditions

The IF function is one of the most commonly used in Excel. The function can test a single condition as well as perform multiple complex logic tests. We can thus control the execution of Excel tasks using the IF function.

This is important since it enables us to perform actions depending on whether they meet certain conditions defined by the IF formula. We can also add data to cells using the function if other cells meet any of the conditions defined in the IF formula.

To add multiple conditions to an IF formula, we simply add nested IF functions.

In this article, you will learn the basics of the IF function and how to come up with an array of IF formulas. You will also learn the nested IF’s function.

Let’s get started!

1. Basic IF formula that tests a single condition

A single IF statement tests whether a condition is met and returns a value if TRUE and another value if FALSE.

IF (logical test, ‘value_if_true’, ‘value_if_false’)

In the example below, let us test whether the price is less than or equal to 2000. If the condition is true return cheap, otherwise return expensive!

Go to Cell C2 and type '=IF(B2<=2000,"CHEAP", "EXPENSIVE")'

The if formula above records or posts the String Good whenever the single condition B2<=2000.

2. IF formula with multiple conditions (Nested IF Functions.

If you have complex data or want to perform powerful data analysis, you might need to test multiple conditions at a time. You might need to perform a logical test across multiple conditions.

In such cases, you have to use an IF statement with multiple conditions/ranges in a single formula. These multiple IF conditions are normally called nested IF Functions.  The function is used if want more than 3 different results. 

The general syntax for IF function with multiple conditions is

=IF (condition one is true, do something, IF (condition two is true, do something, IF (conditions three is true, do something, else do something)))

Commas are used to separate conditions and IF functions in the IF formula. The ")" is used to close all IFs at the End of the formula while the " (" opens all IF functions. The number of "(" should be equal to that of ")".

In our above example, we can include multiple conditions in the IF formula as follows

=IF(B2<=2000,"CHEAP",IF(B2<=6000,"AFFORDABLE",IF(B2<=10000,"EXPENSIVE","LUXURIOUS")))

Press Enter to get the results and use the AutoFill feature to copy the formula to the rest of the cells.

3. IF formula with logical test

You can use an IF statement with AND and OR conditions to perform a logical test. IF your formula has AND function, the logical test returns TRUE if all the conditions are met; otherwise, it will return FALSE. While the OR Function returns TRUE if any of the conditions is met; otherwise it returns FALSE.

3.1 IF with AND Function

In the example below, a student is deemed to pass the exam if he/she has a score of 42 in Maths and 50 in English.

With this information, we can write the condition as follows;

AND (C14>=42, D14>=50)

To know whether the student passed or failed the exam. You can use the IF statement.

= IF(AND (C14>=42, D14>=50),”PASS”,”FAIL”)

Press enter and use the AutoFill feature to copy the formula to the rest of the cells.

3.2 IF with OR Function

The IF with OR Function works the same way as the AND function we have seen above. The only difference is that, if one of the conditions is TRUE then the result will be TRUE.

Follow the above process to apply the OR Function.

In cell E14, type = IF(OR (C14>=42, D14>=50),”PASS”,”FAIL”)

Press enter to get the results.

3. IF with ISNUMBER and ISTEXT Functions

You can use ISNUMBER and ISTEXT formula to find whether the cells have text value, number, or blank.

=IF(ISTEXT(A1), "Text", IF(ISNUMBER(A1), "Number", IF(ISBLANK(A1), "Blank", "")))

I hope you found this tutorial useful.

%d bloggers like this: