AMZ DIGICOM

Digital Communication

AMZ DIGICOM

Digital Communication

Excel IF AND function: explanation and examples

PARTAGEZ

A tool often used in spreadsheets is the combination of IF and AND functions. This allows you to create targeted queries that check multiple conditions and provide immediate results. To learn how the IF and AND functions work when nested in Excel and in which situations to use this combination, read the rest of this article.

Excel with Microsoft 365 for Business and IONOS!

Use Excel to organize your data and save time – included in all Microsoft 365 packages!

Office Online

OneDrive with 1TB

24/7 support

These instructions are valid for Microsoft 365, as well as Excel 2021, 2019, and 2016 versions.

Excel Nested IF and AND Functions: What are they?

In the popular Excel spreadsheet software, the IF function and the AND function can be nested to combine their usefulness. The latter is used to check the accuracy of the values. You define in the corresponding formula the conditions that a value must meet to be displayed as true. If you combine the AND function with the IF function, you trigger actions that Excel must perform based on the result. This is why the combination of IF and AND is a simple way to check your data according to several criteria and thus quickly obtain an overview.

The structure of the nested IF and AND functions is as follows:

=SI(ET(condition1; condition2); valeur_si_vrai;valeur_si_faux)

Imagine that you want to check that the values ​​of a data collection meet conditions A and B. The result C should only be displayed only if both conditions are met, otherwise result D must be displayed. In the formula above, the element condition1 therefore corresponds to condition A and condition2 under condition B. valeur_si_vrai is the result C and the valeur_si_faux corresponds to result D.

If only one of the two conditions must be met for result C, use the IF OR function. With this function, Excel returns result D only if neither condition A nor condition B are met.

Using Excel's IF AND function: practical example

In our example, we use an Excel table with five orders received. We indicate their amount in Euros and if they were carried out by new customers (value oui) or existing customers (value non). In an additional column, we want to know if the order in question is linked to a discount or not:

Excel IF AND function: example with a table
The Excel example should determine which orders are discounted.

THE new customers receive a discount if their order is of at least €50.00. On the other hand, existing customers must pay the full price. To benefit from a reduced price, you must therefore validate two conditions. To determine which orders are discounted using Excel's IF AND function, click cell D2 and insert the following formula into the formula bar:

=SI(ET(B2="oui";C2>49,99);"OUI";"NON")

After confirming the formula with the Enter key, an answer to your query will automatically be displayed in cell D2:

Excel IF AND function: automatic response
Using the IF and AND combination, Excel indicates which order is discounted.

To also get an answer in cells D3 to D6, now drag the green frame to the last row of the table to fill. Since it is a formula with a relative cell referencecells B2 and C2 will automatically be adjusted to B3 and C3, and so on:

Using the IF AND function
Drag the green frame down to get an automatic reply with the autofill feature.

Combine Excel's IF AND function with other functions

Excel formulas have the advantage of being able to be combined with each other if necessary. This gives you the opportunity to facilitate more complex situations. In the following example, we nest the IF AND function with the closely related IF OR function.

Unlike the previous example, the table below contains an additional column that indicates whether the order in question was placed by an employee. Just like new customers, employees receive a discount if their order reaches the value of €50.00. Must therefore either answer only column B either answer column C with oui.

Excel's IF AND function: table to determine the amount of the discount
Use the IF AND function and the IF OR function to find out which order will be reduced.

Use the following formula to start the search:

=SI(ET(OU(B2="oui";C2="oui");(D2>49,99));"OUI";"NON")

The first value of Excel's IF AND function has been replaced by the IF OR function and its two conditions. This time, only one of them must be completed for the discount to be issued. The prerequisite, however, is always that the order value in column D is at least €50.00. After entering the combined function, you get the following responses:

Excel: result after combining several functions
Here are the answers to the query combining the IF AND and IF OR functions.

Excel allows you to nest functions up to 64 levels. Multiple IF statements in a formula are complex to handle and require great concentration. In many cases it is possible to use alternatives such as the SEARCH function or the INDEX function, which however require additional reference tables.

Microsoft 365 for businesses with IONOS!

The latest version of your favorite Office apps on all devices.
Free installation assistance!

Exchange up to 50 GB

OneDrive with 1TB

24/7 support

Télécharger notre livre blanc

Comment construire une stratégie de marketing digital ?

Le guide indispensable pour promouvoir votre marque en ligne

En savoir plus

Souhaitez vous Booster votre Business?

écrivez-nous et restez en contact

Suivez-nous:

© 2024 AMZ DIGICOM All Rights Reserved