How to combine IF, AND and OR functions to filter data

If you only use the IF function, the AND function and the OR function, you cannot see the full usefulness of these functions in Excel. The following article will combine the IF function, the AND function and the OR function to filter data, process data in Excel, please follow along.

How to combine IF, AND and OR functions to filter data Picture 1

First, the article will present through the syntax of the IF, AND and OR functions, if you do not understand each function, you can refer to the articles at the link below.

IF function syntax

= IF (logical_test; [value_if_true]; [value_if_false])

Inside:

1. logical_test : an expression that can be TRUE or FALSE.
2. value_if_true : value you want to return if the logical_test argument is TRUE.
3. value_if_false : value you want to return if the logical_test argument is FALSE.

You can find an example of the IF function here http://TipsMake.vn/ham-if-trong-excel/

AND function syntax

= AND (logical1; [logical2]; .)

Inside:

1. logical1 : required argument, the first condition to be returned as a logical value (TRUE or FALSE) or arrays or references containing logical values.
2. logical2 , .: optional, additional conditions up to 255 conditions.

You can learn more about the AND function here http://TipsMake.vn/ham-and-trong-excel/

OR function syntax

= OR (logical 1; [logical 2]; .)

Inside:

1. logical 1, logical 2 are logical values ​​(either True or False).
2. The function contains up to 255 logical values.
3. The function returns True if one of the arguments has a True value, the function returns False when all of the arguments have a False value.

You can learn more about the OR function here http://TipsMake.vn/ham-or-trong-excel-cach-su-dung-ham-or-va-vi-du-minh-hoa/

Specific examples of combining the IF function, the AND function and the OR function in Excel

Suppose you have the following data table:

How to combine IF, AND and OR functions to filter data Picture 2

You want to calculate the bonus for Sales staff, the bonus is 4% of the total sales provided that Sales> = 80000 or employees in Area 3 with Sales> = 60000.

To do this, in the first cell of the Bonus column, enter the following formula:

= IF (OR (E8> = 80000; AND (D8 = "Area 3"; E8> = 60000)); 0.04 * E8; "No reward")

The condition of the IF function is OR (E8> = 80000; AND (D8 = "Area 3"; E8> = 60000)) if the IF function returns TRUE if either of the conditions in the OR function is true, the AND function returns TRUE if both conditions in the AND function are true.

If the IF function returns TRUE, the employee is rewarded with a bonus of 4% of sales of 0.04 * E8. If the IF function returns FALSE, the string returns 'No reward'.

Your results will be as follows:

How to combine IF, AND and OR functions to filter data Picture 3

Next, copy the function formula down to the cells below to calculate the Bonus for all Salespeople .

How to combine IF, AND and OR functions to filter data Picture 4

Above the article shared how to combine the IF function, the AND function and the OR function in Excel, you can combine the IF function with the AND function or the IF function with the OR function according to the data processing requirements you encounter. To achieve the best effect. Good luck!