The famous program Excel spreadsheet for Microsoft Office not only to organize numbers and text in tables clearly, but includes many features that can be used in a variety of situations, especially when the leaves contain many values. With its more than 400 options , the program allows you to edit or evaluate large amounts of data automatically. The AND function in Excel has always been one of the most popular. With this formula, you can define up to 255 conditions and check if the table values meet them with just a few clicks. Next, we explain how to use the AND function in Excel step by step..
The AND function is one of the logical functions of Excel, so it only allows you to check whether certain conditions are met or not . In other words, it can only return two results: if all conditions are met, Excel returns TRUE. If at least one of them is not fulfilled, the value that is expressed is FALSE.
Suppose you have an Excel spreadsheet that contains many values and you want to know which ones meet certain conditions. Finding them by hand would take a long time. Also, chances are that you overlooked some value and made mistakes. For this reason, you should use the AND function in Excel, the most suitable for automatically checking if certain conditions are met . Next, we explain how to apply it to an Excel spreadsheet step by step..
In this example, we want to know which employees meet all the requirements to get an incentive. For this, there are two conditions : they must have managed to attract at least three new clients and extend two or more contracts. This also implies, for example, if an employee has won five new customers (more than required), but has only succeeded in extending a contract, you will not receive the incentive. Using the AND function in Excel is ideal for this example, because the program can check if all the indicated conditions are optimally met.
After you have defined all the values for the AND function example, you can apply the formula to the table. For the sake of clarity, it is advisable to use different columns for the function and the result. There are two ways to enter the function:
Enter the function in the cell where the result will be displayed . Functions always begin with an equal sign and the name of the function. The arguments of the function are enclosed in parentheses..
This is the basic syntax of the Excel AND function:
=Y([valor_lógico1];[valor_lógico2];?)
If you write? And? in lower case, Excel will correct it automatically. Therefore, it is not necessary to pay attention to the spelling.
The logical values represent the conditions , which are separated by semicolons. The AND function supports between two and 255 conditions. However, it is not recommended to introduce too many: the more conditions a function has nested, the more complex it becomes, which complicates its creation, review and management.
To define logical values or conditions, equals signs (=), greater than (>), and less than (<) are used. In our example, the first logical value represents the condition that the employee has obtained at least three new customers. Therefore, the value in column B must be equal to or greater than three. To define it, enter? B2> = 3 ?. Logical value 2 is the condition that the employee has also extended at least two contracts. To do this, write? C2> = 2 ?. This is how the complete AND function would look in our example:
=Y(B2>=3;C2>=2)
Press the return key on your keyboard to confirm the formula. When you do, Excel will no longer display the function itself, but the result. In our example, the first employee meets only one of two requirements. Therefore, the value FALSE is generated:
If you prefer, there is the option of entering the Y function in Excel through a dialog box. To open it, select the menu? Formulas? and hit? Insert function? or left click on the corresponding icon in the toolbar:
Excel opens the ? Insert Function? Dialog box. . To select the logical function AND you must select the category? Logic? in the drop-down list. When you select it, it will be marked in blue. Confirm the section by clicking on? Accept ?
If the function you want is not in the list, select? Everything ? in the drop-down menu of? Select category ?. Excel will show you a list of all available functions , through which you can navigate with the keyboard arrows to choose the "AND" option. Click on the function with the left mouse button to mark it in blue and confirm with? Accept ?
Then Excel opens the ? Function Arguments? Dialog box. , where the arguments are defined on separate lines . During this process, the program already shows if the conditions are met. When you have entered them all, confirm them by clicking on "OK". Excel creates the formula and displays the result in the cell.
It is not necessary to insert the AND function by hand on each line. If the conditions you entered apply to all values (in our example, all employees), Excel can pass the function to all rows with just a few clicks .
To do this, first select the cell that contains the AND function. Then, left-click on the green square that appears on the border of the cell. Hold down the button and drag the function over all the cells to which it has to be applied. In our example, it will be from cell D2 (employee 1) to D11 (employee 10).
Excel transfers the AND function automatically and adapts the formula accordingly , checking if the conditions are met for each employee:
With the IF function, you can check if a requested value matches an expected value. The basic syntax of this formula is as follows:
=SI(prueba_lógica;[valor_si_verdadero];[valor_si_falso])
The IF function is very similar to AND. Its two most important differences are the following:
With the AND function in Excel, the only possible results are TRUE or FALSE. In the case of SI, on the other hand, the parameters? Value_if_true? and ? value_if_false? to define the results that Excel displays in the cell after the calculation is done.
The SUMIF function offers the possibility of adding only some values. If you are interested, you can learn how to use it in our article on the SUMIF function.
In our example table, you can use the IF function to define these conditions: if an employee gets at least five new customers, they will receive an incentive of 100 euros. If the employee does not reach the goal, he will not get any incentive. The IF formula would be as follows:
=SI(B2>=5;100;0)
Excel allows you to combine multiple functions into a single formula . Combining the IF and AND functions offers two benefits : defining multiple conditions and customizing the results that are displayed.
This would be the basic syntax that results from combining the two functions:
=SI(Y([valor_lógico1];[valor_lógico2];[valor_lógico3];?);[valor_si_verdadero];[valor_si_falso])
In our example, the actual formula would have the following structure:
=SI(Y(B2>=3;C2>=2);100;0)
Once the combined formula is confirmed, we get the following return values in our example table:
