IF Functions in MS Excel – Shiksha Online

IF Functions in MS Excel – Shiksha Online

7 mins read791 Views Comment
clickHere
Rashmi
Rashmi Karan
Manager - Content
Updated on Feb 12, 2024 14:17 IST

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.

2022_03_IF-functions-in-Excel-2.jpg

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

Main data set

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.

Power Query in Excel – Shiksha Online
Power Query in Excel – Shiksha Online
Power Query is a pretty powerful tool that allows you to import and clean millions of rows into a data model. Learn how to use Power Query in Excel through...read more
Introduction to MS Excel – A Beginner’s Guide
Introduction to MS Excel – A Beginner’s Guide
Microsoft Excel has been around for decades and is one of the most desired skills if you are considering a career in data science or analysis. But there are still...read more
Cell References in Excel – Shiksha Online
Cell References in Excel – Shiksha Online
References in Excel are a fundamental part of the spreadsheet. They refer to each cell of a workbook and thus access the value contained in any of them. Cell references...read more

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.  

MIN and MAX Functions in MS Excel
MIN and MAX Functions in MS Excel
Learn about MIN and MAX functions in MS Excel in this blog.
Sum Function in Excel – Learn Through Examples
Sum Function in Excel – Learn Through Examples
In continuation to our previous tutorial on MIN and MAX functions in Excel, we move on to another important basic function, the SUM function. Learn about the SUM function in...read more
MEDIAN Function in MS Excel
MEDIAN Function in MS Excel
The article covers the MEDIAN function in MS Excel. It is in continuation to the MS Excel basic functions series.

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)

How to use SUMIF in Excel, 1
How to use SUMIF in Excel, 2

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”)

How to use SUMIF in Excel 2

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 SUMIF in Excel 3

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 in Excel 1

=COUNTIF(E3:E13, “Clothing”)

COUNTIF in Excel 2

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”)

COUNTIF in Excel 4
COUNTIF in Excel 5

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. 

Average Function in Excel – Formula and Examples
Average Function in Excel – Formula and Examples
The AVERAGE function is a popular Excel function used for various purposes, such as calculating average sales, average student scores, average monthly expenses, etc. It provides a quick and efficient...read more
COUNT Function in Excel – Shiksha Online
COUNT Function in Excel – Shiksha Online
Read on the article to understand how COUNT, COUNTA, and COUNTIF functions work in MS Excel.
TRIM Function in Excel – Shiksha Online
TRIM Function in Excel – Shiksha Online
If you are dealing with datasets from Excel, you know that you have to deal with characters that you’d rather have removed, such as unnecessary spaces. This happens especially if...read more

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)

AVERAGEIF in Excel 1
AVERAGEIF in Excel 2

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”)

AVERAGEIF in Excel 3
AVERAGEIF in Excel 4

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".

IF function

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.

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio