EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Functions Excel AVERAGEIF
Secondary Sidebar
Excel Functions
  • Excel Functions
    • Excel Column Total
    • VLOOKUP to Return Multiple Values
    • User Defined Function in Excel
    • Excel SWITCH Function
    • Excel FV Formula
    • Excel AVERAGEIF
    • Simple Formula in Excel
    • Excel Scatter Chart
    • Excel Bar Chart
    • LINEST Excel Function
    • RANK Excel Function
    • Excel MOD Function
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Certification Course
  • EXCEL Certification COURSE

Excel AVERAGEIF

By Madhuri ThakurMadhuri Thakur

AVERAGEIF in excel

Excel AVERAGEIF (Table of Contents)

  • Introduction to AVERAGEIF in Excel
  • How to use the AVERAGEIF Function in Excel?

Introduction to AVERAGEIF in Excel

AVERAGEIF function in Excel is used to calculate the average of numbers based on defined criteria. For example, we have sales data of 4 product, and we want to find out the average sale of any product from the entire data or selected portion of data. For that, we use AVERAGEIF, whereas per syntax, we first select the range of all the products and then give select the criteria by selecting the product name for which we want to find average sale and then select the complete range of sold quantities.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,325 ratings)

Now, we have known the exact AVERAGEIF function; we will now learn how to write a formula using the formula’s function and syntax.

The syntax of the AVERAGEIF function

AVERAGEIF syntax

Here is the explanation of all the elements of the syntax.

  • Range – A range of cells on which the criteria or condition is to be applied. The range can include a number, cell references and names as well. In case there is no “Average-range” argument, this range will be used for the calculation of average.
  • Criteria – This is a condition based on which cells will be averaged. It could be a number, cell reference, text value, logical statement (like “<5”) or expression.
  • average_range – The cell range to average. It is optional. In the absence of any average_range, the range is used to calculate the average.

How to use the AVERAGEIF Function in Excel?

Now, we will learn how to use the AVERAGEIF function in Excel with the help of various examples. It is an inbuilt function in Excel.

You can download this AVERAGEIF Excel Template here – AVERAGEIF Excel Template

AVERAGEIF in Excel – Example #1

The AVERAGEIF function in Excel calculates the average of cells that exactly match the criteria or the condition specified.

Below you will see a table containing the product name and the units that are sold.

In this example, we need to find the average units sold for product B.

AVERAGEIF Example 1-1

We can directly type the formula by starting it with = and typing AVERAGEIF, or we can also select the function from the ribbon as shown below:

AVERAGEIF Example 1-2

Now Function Arguments Box will appear. Then select Range: A2:A13, Criteria: B15, Average_range: B2:B13.

AVERAGEIF Example 1-4

Press OK. We get the below result which is the average units sold for product B.

AVERAGEIF Example 1-5

We can also type in “B” instead of cell reference in the formula as shown below in cell B15:

AVERAGEIF Example 1-4

Explanation of the example :

Range: We have taken A2: A13 as the range on which our criteria (B) will be applied.

Criteria: Since we wanted to know the average units sold for B, so B is our criteria.

Average _range: B2: B13 is the range of cells from which Excel will do the average.

Taking our last example. Suppose we want to know the average units sold for D product.

AVERAGEIF Example 2-1

We will get the average units sold for D product = 194.33

AVERAGEIF in Excel – Example #2

We have a sample data table that contains the score of students for the second and third semesters.

Now, if we want to calculate the average second-semester score from the above data, we can do it by writing the formula as shown below:

AVERAGEIF Example 2-2

We will write B2:B7 as range and make it absolute by pressing F4. Then, we will write “second” as criteria and C2:C7 as average_range and make it an absolute range. We will get the average third-semester score by using cell reference B6 instead of the word “Third” as criteria.

AVERAGEIF Example 2-3

And we will press the Enter key to get the result.

AVERAGEIF Example 2-4

AVERAGEIF in Excel – Example #3

In this sample data, we have sales data for cities in different zones.

AVERAGEIF Example 3-1

If we want to find the average sale of a particular zone, say, for instance, West. We can get average sales as below:

AVERAGEIF Example 3-2

Press the Enter key to get the result.

AVERAGEIF Example 3-3

So these examples must have given you a fair understanding of the use of the AVERAGEIF function.

AVERAGEIF in Excel – Example #4

AVERAGEIF using a wildcard in-text criteria

We use a wildcard in the AVERAGEIF function when the text criteria are partially met or are preceded or followed by any other word.

Below is a sample sales data of confectionary items. If we want to know the average sales for all cakes instead of a specific cake, we then use a wildcard(*). Like in our case, the keyword cake is preceded by other words like chocolate, fruit, plain, etc. We can overcome this limitation by using an asterisk sign before the word “cake” to fetch the cakes’ sales data.

Similarly, we can add an asterisk sign after the search keyword if some other word follows it.

So we will write the formula as below and will add * sign before our criteria. In this case, our criteria are cakes.

AVERAGEIF Example 3-4

After pressing the Enter key, we see the result.

AVERAGEIF Example 3-5

If any other word follows the keyword criteria, just add an asterisk sign after the keyword /criteria. Like “cake*.”

Likewise, if you want to average the sales of all other items apart from cakes, you can use the below formula using a wildcard. Which is “<>*cake”.

AV Example 3-6

Press the Enter key to get the result.

AV Example 3-7

AVERAGEIF in Excel – Example #5

AVERAGEIF using logical operations or numeric criteria

Many a time, we have to average cells based on logical statements like “>100” or “<100”. Or as per numeric criteria. How will we do this?

We will understand this with the help of an example.

AV Example 4-1

Taking our sample data. Suppose we want to know the average of units sold above 250. We will find this out as below:

AV Example 4-2

After pressing the Enter key, we will get a result, as shown below.

AV Example 4-3

So the average of units sold above 250 belongs to products E and D. Similarly, if we want to know the average units sold below 100. We can find this, as shown below:

AV Example 4-4

After pressing the Enter key will give us the result.

AV Example 4-5

This is how we can use AVERAGEIF with logical statements as criteria.

Things to Remember

  • If the criteria for calculating the average numbers are text or logical expression, they should always be written in double-quotes.
  • In case no cell in the range meets the criteria for average or the range is empty, the function will return #DIV0! Error.
  • If the cell as criteria is blank, the function will take it as a 0.
  • Excel treats the default “criteria” operand as equals to; other operands may include >,<,<>, >=and <= as well.
  • Suppose any of the cells in a range has True or False. The function will ignore it.

Recommended Articles

This has been a guide to AVERAGEIF in Excel. Here we discuss how to calculate Average using AVERAGEIF Function in Excel and practical examples, and a downloadable excel template. You can also go through our other suggested articles–

  1. MAX IF Function in Excel
  2. Weighted Average in Excel
  3. Excel Average Formula
  4. Excel AVERAGE Function
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download AVERAGEIF Excel Template

EDUCBA

Download AVERAGEIF Excel Template

EDUCBA

डाउनलोड AVERAGEIF Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more