IF function in Excel. Examples (with multiple conditions)

One of the main advantages of Excel spreadsheets is the ability to program the functionality of a particular document. As most people know from school computer science lessons, one of the main components that allows you to put this into practice is logical operators. One of them is the IF operator, which provides for the execution of certain actions when certain conditions are met. 

For example, if the value matches a certain one, then one label is displayed in the cell. If not, it’s different. Let’s look at this effective tool in more detail in practice.

IF function in Excel (general information)

Any program, even if it is small, necessarily contains a sequence of actions, which is called an algorithm. It may look like this:

1. Check the entire column A for even numbers.
2. If an even number is found, add such and such values.
3. If an even number is not found, then display the inscription “not found”.
4. Check if the resulting number is even. 
5. If yes, then add it to all the even numbers selected in paragraph 1.

And even if this is only a hypothetical situation, which is unlikely to be needed in real life, the execution of any task necessarily implies the presence of a similar algorithm. Before using the function IF, you need to have a clear idea in your head about what result you want to achieve. 

Syntax of the IF function with one condition

Any function in Excel is performed using a formula. The pattern by which data must be passed to a function is called syntax. In the case of the operator IF, the formula will be in this format.

=IF (logical_expression, value_if_true, value_if_false)

Let’s look at the syntax in more detail:

1. Boolean expression. This is the condition itself, the compliance or non-compliance of which Excel checks. Both numerical and textual information can be checked.
2. Value_if_true. The result that will be displayed in the cell if the data being checked meets the specified criteria.
3. value_if_false. The result that is displayed in the cell if the data being checked does not match the condition.

Here is an example for clarity.

Here the function compares cell A1 with the number 20. This is the first paragraph of the syntax. If the content is greater than this value, the value “greater than 20” is displayed in the cell where the formula was written. If the situation does not correspond to this condition – “less than or equal to 20”.

If you want to display a text value in a cell, you must enclose it in quotation marks.

Here is another situation. In order to be eligible to take an examination session, students must pass a test session. The students managed to win credits in all subjects, and now the last one remains, which turned out to be decisive. Our task is to determine which of the students are admitted to the exams and which are not.

Since we want to check for text and not a number, the first argument is B2=”cons.”.

IF Function Syntax with Multiple Conditions

Often, one criterion is not enough to check the value against. If you need to consider more than one option, you can nest functions IF one into the other. There will be several nested functions.

To make it clearer, here is the syntax.

=IF(logical_expression, value_if_true, IF(logical_expression, value_if_true, value_if_false))

In this case, the function will check two criteria at once. If the first condition is true, the value obtained as a result of the operation in the first argument is returned. If not, the second criterion is checked for compliance.

Here’s an example.

And with the help of such a formula (shown in the screenshot below), you can analyze the performance of each student.

As you can see, one more condition was added here, but the principle has not changed. So you can check several criteria at once.

How to extend the IF functionality using AND and OR operators

From time to time there is a situation to check immediately for compliance with several criteria, and not use logical nested operators, as in the previous example. To do this, use either the function И or function OR depending on whether you need to meet several criteria at once or at least one of them. Let’s take a closer look at these criteria.

IF function with AND condition

Sometimes you need to check an expression for multiple conditions at once. For this, the AND function is used, written in the first argument of the function IF. It works like this: if a is equal to one and a is equal to 2, the value will be c.

IF function with “OR” condition

The OR function works in a similar way, but in this case, only one of the conditions is true. As much as possible, up to 30 conditions can be checked in this way. 

Here are some ways to use functions И и OR as function argument IF.

Comparing data in two tables

From time to time it is possible to compare two similar tables. For example, a person works as an accountant and needs to compare two reports. There are other similar tasks, such as comparing the cost of goods of different batches, then, the assessments of students for different periods, and so on.

To compare two tables, use the function COUNTIF. Let’s look at it in more detail.

Let’s say we have two tables containing the specifications of two food processors. And we need to compare them, and highlight the differences with color. This can be done using conditional formatting and the function COUNTIF. 

Our table looks like this.

We select the range corresponding to the technical characteristics of the first food processor.

After that, click on the following menus: Conditional formatting – create a rule – use a formula to determine the formatted cells.

In the form of a formula for formatting, we write the function =COUNTIF (range to compare; first cell of first table)=0. The table with the features of the second food processor is used as a comparison range.

You need to make sure that the addresses are absolute (with a dollar sign in front of the row and column names). Add =0 after the formula so that Excel looks for exact values.

After that, you need to set the formatting of the cells. To do this, next to the sample, you need to click on the “Format” button. In our case, we use the fill, because it is the most convenient for this purpose. But you can choose any formatting you want.

We have assigned a column name as a range. This is much more convenient than entering the range manually.

SUMIF function in Excel

Now let’s move on to the functions IF, which will help to replace two points of the algorithm at once. The first one is SUMMESLEY, which adds two numbers that meet a certain condition. For example, we are faced with the task of determining how much money should be paid per month to all sellers. For this it is necessary.

1. Add a row with the total income of all sellers and click on the cell that will contain the result after entering the formula. 
2. We find the fx button, which is located next to the line for formulas. Next, a window will appear where you can find the necessary function through the search. After selecting the operator, you need to click the “OK” button. But manual input is always possible.
   

   

3. Next, a window for entering function arguments will appear. All values ​​can be specified in the corresponding fields, and the range can be entered through the button next to them.
   

   

4. The first argument is a range. Here you enter the cells that you want to check for compliance with the criteria. If we talk about us, these are the positions of employees. Enter the range D4:D18. Or just select the cells of interest.
5. In the “Criteria” field, enter the position. In the case of us – “seller”. As the summation range, we indicate those cells where the salaries of employees are listed (this is done both manually and select them with the mouse). Click “OK”, and we get the finished calculated wages of all employees who are sellers.

Agree that it is very convenient. Is not it?

SUMIFS function in Excel

This function allows you to determine the sum of values ​​that meet multiple conditions. For example, we were given the task of determining the total salary of all managers working in the southern branch of the company.

Add a row where the final result will be, and insert the formula in the desired cell. To do this, click on the function icon. A window will appear in which you need to find the function SUMMESLIMN. Next, select it from the list and the familiar window with arguments opens. But the number of these arguments is now different. This formula makes it possible to use an infinite number of criteria, but the minimum number of arguments is five. 

Only five can be specified through the argument input dialog. If you need more criteria, then they will have to be entered manually according to the same logic as the first two.

Let’s look at the main arguments in more detail:

1. Summation range. Cells to be summed.
2. Condition range 1 – the range that will be checked for compliance with a certain criterion. 
3. Condition 1 is the condition itself.
4. Criteria range 2 is the second range that will be checked against the criterion.
5. Condition 2 is the second condition.

Further logic is similar. As a result, we determined the wages of all managers of the Southern Branch.

COUNTIF function in Excel

If you need to determine how many cells fall under a certain criterion, use the function COUNTIF. Let’s say we need to understand how many salespeople work in this organization:

1. First, add a line containing the number of sellers. After that, you need to click on the cell where the result will be displayed.
2. After that, you need to click on the “Insert Function” button, which can be found in the “Formulas” tab. A window will appear with a list of categories. We need to select the item “Full alphabetical list”. In the list, we are interested in the formula COUNTIF. After we select it, we need to click the “OK” button.
   

   

3. After that, we have the number of salespeople employed in this organization. It was obtained by counting the number of cells in which the word “seller” is written. Everything is simple. 

COUNTSLIM function in Excel

Similar to the formula SUMMESLIMN, this formula counts the number of cells that match multiple conditions. The syntax is similar but slightly different from the formula SUMMESLIMN:

1. Condition range 1. This is the range that will be tested against the first criterion.
2. Condition 1. Directly the first criterion.
3. Condition Range 2. This is the range that will be tested against the second criterion. 
4. Condition 2.
5. Range conditions 3.

And so on.

So the function IF in Excel – not the only one, there are several more of its varieties that automatically perform the most common actions, which greatly simplifies a person’s life. 

Largely due to the function IF Excel spreadsheets are considered programmable. It’s more than just a simple calculator. If you think about it, then the function IF is a cornerstone in any kind of programming.

So if you learn how to work with large amounts of data in Excel, it will be much easier to learn programming. Thanks to logical operators, these areas really have a lot in common, although Excel is more often used by accountants. But the mechanism for working with data is largely the same. 

Function in the right hands IF and its variations allow you to turn an Excel sheet into a full-fledged program that can act on complex algorithms. Understanding how the function works IF is the first step towards learning macros – the next step in more flexible work with spreadsheets. But this is already a more professional level.