Excel AND function
In Excel, the AND function tests one or more cells with separate conditions, if the conditions are all met, then TRUE value is returned; if any of the conditions is evaluate false, a FALSE value will be displayed. It is often used with other Excel functions.
The syntax for the AND function in Excel is:
Test multiple conditions, and returns TRUE if all arguments are met, otherwise, FALSE is returned.
Example 1: Use AND function only
Supposing, there are three columns which contain products, orders and salesman, now, you want to check which rows meet these conditions: Product = KTE, Order > 100, Salesman = Nicol.
Please apply the following formula into a blank cell where you want to put the result, and then drag the fill handle down the cell to apply the formula, and you will get the following result:
Example 2: Use IF and AND functions together
It is a common usage for us to combine the IF and AND functions together.
For example, you have a table contains the students’ test scores, both of the first and the second subject scores must be equal to or greater than 60, then the student passes the final exam, otherwise, fail the exam. See screenshot:
To check if the student passes the exam or not, using the IF and AND functions together to solve it, please use this formula:
This formula indicates that, it will return Pass, if a value in column B >=60 and a value in column C >=60. Otherwise, the formula returns Fail. See screenshot:
Example 3: Use IF and AND functions together for calculation
The IF AND functions can also perform calculations based on whether AND function is TRUE or FALSE.
If you want to calculate the bonus or commission for your company staff based on their sales performance, for instance, if the person’s sales equals or greater than $50000, and his performance is A, then he will get 3% bonus, otherwise, he will no bonus. How to calculate the bonus in Excel quickly and easily?
To calculate the bonus, please apply this formula:
The above formula indicates, IF Sales are equal or greater than 50000, AND Performance is A grade, then multiply Sales by 3%, otherwise it will return 0.
And then, you will get the following result as you need:
Tip: If you want to calculate the bonus for the sales which are less than 50000 instead of returning 0, for example, multiply Sales by 1.5%, when Sales less than 50000, you should use this formula: