Excel Conditional Functions

by

Last updated on April 15th, 2023 at 11:18 am

Grammarly Writing Support

Excel conditional functions allow us to make calculations on part of the data by setting limits. So if you have a large volume of information on your customers, but you need to count the persons living in Trinidad, spreadsheets allow you to do this in a few seconds without having to move the data around. As such conditional functions are very useful.

In this article, we will cover several Excel conditional functions. Keep reading to learn about the

  • If function;
  • Countif function; and
  • Sumif function.

If you are new to spreadsheets, you should also check out our article, introduction to spreadsheets.

Have to complete your SBA but are struggling to write good grammar, spelling or punctuation? Get the best spelling, grammar and punctuation checker to begin writing like a professional. Go ahead and get the free tool today.

❗❗In the English language, the word criteria usually refer to multiple conditions. It is the plural of criterium. However, in Excel, the word criteria (never criterium) is always used to refer to the condition in the function.

The IF function in Excel (Excel conditional functions – IF)

In Excel, we use the if function to do a calculation on a selected set of data. For instance, you wish to go to the upcoming school dance, but part of the requirements is that all students desirous of going must sell tickets to the school bake sale. Only the students who have managed to sell 20 or more tickets will be allowed to attend the dance.

This is one such condition, and we can express this information in an Excel spreadsheet and extract the list of students who are eligible to attend the dance using the IF function. We have done this in the table below.

Excel conditional function showing how the if function works. The condition reads =if(D3>=20, "Yes", "No")
Figure 1

This Excel conditional function has three parts a criterium, then a value if true, followed by a value if false. Each time you put a comma, it signals to the software that you are moving to the next part of the function.

For clarity

  • The criterion is: D3>=20;
  • The value if true is: Yes; and
  • This value if false is: No.

When you use this Excel conditional function, the computer checks the cell address to see if the value it contains meets the criteria. In the formula above, it is checking to see if the value in D3 is more than or equal to 20. If this is true, the program enters (Yes) in the cell. If the condition is not true, then the value if false (No) is entered in the cell.

You can also write this if function with a criteria and one value. Like this =IF(D3>=20, “Yes”). If you do this when the value does not meet the criteria, the program will simply put FALSE as the answer in the cell.

Did you know that functions and formulas in spreadsheets are not the same thing? Visit introduction to spreadsheet formulas and what is a spreadsheet function to learn more.

The IF function with a calculated criteria

Sometimes the IF function can also have a calculated criteria. Example: Let’s imagine that instead of selling 2 tickets, students are to sell 20% of the tickets they received in order to get into the dance. In this case, you would write the function =IF(D3=20/100*C3, “Yes”, “No”) in cell E3. As you can see in this function, the value in D3 is compared to a value that will result by finding 20% of C3.

As with the earlier example, commas separate the criteria, true and false components.

Did you know that absolute cell reference can prove quite useful in the workplace? Learn what is absolute and relative cell references and how to use them. Additionally, you should visit our article on advanced spreadsheet functions.

The SUMIF function (Excel conditional functions – SUMIF)

The SUMIF function, as the name suggests, finds the total of the contents of a set range based on a predefined criteria. Look at the table below to see the SUMIF function. The first part of the function shows a range on which the criteria is applied. The second part shows the criteria, while the third part identifies the range where we will calculate the total.

This image displays the SUMIF function.
Figure 2

So in simple English, the SUMIF function above says to check all the values in the range E3 to E7. For all those that return the value Yes, add the corresponding values in the range D3 to D7. So only the values 23, 30, and 21 will be added together.

Are you interested in learning some Excel shortcuts? Visit Microsoft Excel features and shortcuts.

The COUNTIF function in Excel

Another conditional function is the COUNTIF. In Excel, when you use the COUNTIF function, the program checks all of the contents of the specified range and counts those that meet the criteria. Take a look at the table below as an example:

A spreadsheet that counts all the yes values in the range. It reads =count(E3:F3, "Yes", "No").
Figure 3

In the above table, we can see the COUNTIF function in use. This function uses a range and criteria. You need to first specify the range you wish to count, then indicate what values in that range you wish to count by writing the criteria. Using the COUNTIF function in the image above, the spreadsheet will count and print all of the cells that have the value “Yes”.

Before you go

We try our best to deliver clear, accurate content. However, if you have any questions or comments, be sure to leave them in the comment section below, and we will get back to you.