IF Functions in MS Excel – Shiksha Online

Learning Microsoft Excel is all about adding more and more formulas and functions to your toolkit. To excel in MS Excel, you need to learn more and more formulae and practice more on the spreadsheets. In this tutorial, you’ll learn how to use three powerful Excel formulas: SUMIF, COUNIF, and AVERAGEIF. Learn IF Functions through real-life datasets.

To start learning these formulae, we need to have a dataset to get started. The below dataset includes expenses for a given month.

In the screenshot above, you can see that we have a list of transactions on the left side. If I want to control my expenses, I can use these three IF Functions to monitor it. 

On the right side, the Taxes Expense box uses the three formulas to help me keep track of my spending:

COUNTIF – Count the number of items that meet a condition, such as counting the number of times a name appears in a list. Here, it will count the number of times “Taxes” appears in the list.

SUMIF – Add values ​​if a condition is met, such as adding all purchases in a category. Here, it calculates the total spend for items tagged “Taxes”

AVERAGEIF – It averages the values from the given data. In the above dataset, AVERAGEIF averages all my “Taxes” expenses in the list.

These IF Functions allow you to add logic to your spreadsheet and add efficiency.

How to Use SUMIF in Excel?

Use the tab titled SUMIF in the free example book for this section of the tutorial.

The SUMIF formula provides an easier way to add values ​​that satisfy a rule. We can add a list of values ​​from a certain category or all values ​​greater than or less than a specific amount.

This is how the SUMIF formula works:

=SUMIF(range, criteria, [sum_range])

To understand how the formula works, let’s check the list of Tax transactions from the sheet.

I have a list of transactions, and I am going to use the SUMIF formula to help me control my expenses.

I want to know two things:

• The total of what I spent in restaurants during the month
• All purchases over 5000 per month from any category

Instead of manually adding data, we can write two SUMIF Formulas to automate the process.  

Total Taxes Expenses

To find my total tax expense, I’ll roll up all the values ​​with the “Taxes” expense type, which is in column B. The formula used here will be – 

=SUMIF(E3:E13, “Taxes”, F3:F13)

Notice that each section is separated by a comma. This formula does three key things:

• See what’s in cells E3 through E13 for the expense category
• Use “Taxes” for the criteria of what to summarize
• Use the values ​​in cells E3 through E13 to add the amounts

In this example, I am adding all the values ​​of the “Taxes” expense type.

When I press Enter, Excel calculates my total expenses on Taxes. Using SUMIF, it’s easy to create these quick statistics to help you monitor data of certain types.

Purchases over 5000

We’ve checked for a specific category, but now let’s summarize all values ​​greater than A quantity from any category. In this case, I want to find all purchases that were more than 5000

I will write the SUMIF formula to find the sum of all purchases greater than 5000:

=SUMIF(F3:F13, “>5000”)

In this case, the formula is a bit simpler: since we’re summing the same values ​​we’re testing (F3 through F13), we only need to specify those cells. We add a comma and “>5000” to only sum values ​​greater than 5000.

How to Use COUNTIF in Excel

As we discussed in our previous article, COUNT will count the number of times something appears in a given data set.

Here is the general format for the COUNTIF formula:

=COUNTIF(range, criteria)

Using the same data set, let’s count two key pieces of information:

• The number of clothing purchases I made in a month.
• The number of purchases of 1000 or more
• Number of clothing purchases

We will now look at the expense type and count the number of “Clothes” purchases in my transactions.

The final formula will be:

=COUNTIF(E3:E13, “Clothing”)

That formula looks at the “Expense Type” column, counts the number of times you see the clothing, and counts it. The result is 2.

Number of Purchases Above 1000

Now, let’s count the number of transactions that were 1000 or more

Here is the formula I will use:

= COUNTIF (C2: C17, “> 100”)

This is a simple two-part formula: point Excel at the list of data and the rule to count. In this case, we check cells F3 through F13 for all values ​​greater than ₹ 1000. 

How to use AVERAGEIF in Excel

Finally, let’s see how to use an AVERAGEIF formula. By now, you know that the AVERAGEIF function can be used to average specific values based on a condition we provide.

The format for an AVERAGEIF formula is:

=AVERAGE.IF(range, criterion, [average range])

The format of the AVERAGEIF formula is very similar to the SUMIF formula.

Let’s use the AVERAGEIF formula to calculate two key statistics about my spending:

• The average of the restaurant’s expenses 
• The average of all expenses less than ₹ 1000

Average Restaurant Expenses 

To average my restaurant expenses, we will write the AVERAGEIF formula to average all amounts based on category.

=AVERAGEIF(E3:E13, “Restaurant”, F3:F13)

There are three parts to this formula, each separated by a comma:

E3:E13 specifies the cells to check for a condition. Since the type of expense is specified in this column.

• “Restaurant” gives the formula something to look for
• Finally, F3:F13 are the cells that average our calculation

Here I am using an AVERAGEIF to calculate the cost of my annual trip to the restaurant.

In the end, Excel averages the expenses of my trips to restaurants. I have given you that formula.

You can also try this formula by replacing “Restaurant” with another category, such as “Clothes.”

Average Expenses Less Than ₹ 1000

If I’m keeping an eye on my smaller purchases and want to know my average, I can write an AVERAGEIF for all purchases less than an amount.

Here is the formula to calculate that average –

= AVERAGEIF (C2: C17, “<25”)

This simple formula only checks the values ​​in column F and averages all values ​​greater than ₹ 1000.

Recap 

In this tutorial, you learned how to use three conditional math formulas to review your data using IF Function Excel. Whether adding, counting or averaging data, these functions are advanced Excel skills you can use.

The key takeaway for all the IF Functions in this tutorial is that you can apply conditions to your calculations in Excel. 

Keep Learning!

Read More in Excel

MIS Reports in Excel | Excel Interview Questions | Basic Excel Formulas | Difference Between Formula and Function in Excel | Average Function in Excel | Introduction to MS Excel | HLOOKUP in Excel | ROW and COLUMN in Excel | Financial Modelling in Excel | Percentage In Excel | Remove Duplicates In Excel | Merge Cells in Excel | MIN and MAX Functions in Excel | Combine Text Strings in Excel | Import Data from PDF to Excel | Pivot Table In Excel | How To Enable Macros In Excel | How to Import Text Files to Excel | TRIM Function in Excel

FAQs - IF functions in Excel

What is the IF function in Excel, and how does it work?

The IF function is a logical function in Excel that allows you to perform conditional checks on data. It evaluates a specified condition and returns one value if it is TRUE and another value if it is FALSE.

How do I use the IF function to perform a basic conditional check?

To use the IF function, you specify a logical test followed by the value to return if the test is TRUE and the value to return if the test is FALSE. For example, =IF(A1>10, "Yes", "No") returns "Yes" if the value in cell A1 is greater than 10. Otherwise, it returns "No".

Can I nest IF functions within each other in Excel?

Yes, you can nest IF functions to perform more complex conditional checks. You can use multiple IF functions within each other to create nested logical tests and return different values based on the results.

How do I handle multiple conditions using the IF function?

You can use logical operators like AND, OR, and NOT to handle multiple conditions within the IF function. For example, =IF(AND(A1>10, B1="Yes"), "True", "False") returns "True" if both conditions are met. Otherwise, it returns "False".

Can I use text and logical values as arguments in the IF function?

The IF function in Excel can handle text, numbers, logical values (TRUE/FALSE), and even cell references as arguments. You can use any combination of these data types in your IF function.

What happens if the logical test in the IF function evaluates to an error?

If the logical test in the IF function evaluates to an error, the function will return an error value (e.g., #VALUE!, #DIV/0!, #REF!, etc.). You can use error-handling techniques like IFERROR or ISERROR to handle these situations.