How to Use the IF(AND) Formula in Excel

The IF function can be used to perform a logical comparison between two values by testing for a condition and returning the result based on whether that condition is TRUE or FALSE. You can also use the AND function, one of the logical functions, in combination with the IF function to determine if multiple conditions have been met and return the value TRUE or FALSE accordingly.

Formula:

Formula Explanation:

• Logical_test (required): The condition you want to test.
• Value_if_true (required): The value that you want returned if the result of logical_test is TRUE.
• Value_if_false (optional): The value that you want returned if the result of logical_test is FALSE.

Example:

The General Manager of a second-hand car dealership wants to know which salesperson met the Company’s sales goal.

The sales goal is used to determine which salespersons are eligible for the company bonus of 5%. The sales goal has two criteria that need to be met. The two criteria are:

a) Units sold during the month must exceed 6 cars.

b) Total sales $ must be greater than $10k for the month.

The Finance Analyst produces a sales report from the ERP system with sales by salesperson for the current month and the report provides details on the number of sales made and total sales dollars.

Solution:

Based on the below analysis, only Erika and Josephine have met the two criteria and is eligible for the 5%-month-end bonus.

Here,

I. Logical_test (AND) formula was used to set a condition that BOTH criteria should be met before a value will be set as “true”.

II. Value_if_true formula was used to calculate the “Bonus” if two criteria was satisfied.

III. Value_if_false formula was used to set the cell as “0” (Zero) if any or both of the criteria were not satisfied.

• As seen from above, we just applied 2 logical tests within this formula but as shown on screen here you could put a third logical test and there are 3 dots at the end of the expression preview suggesting that you could add several more depending on your need.
• In our case, we only had 2 conditions that needed to be met in order for the bonus to be paid out.

I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!