What is the Averageif formula?

Toggle navigation GoSkills Search for courses or lessons SearchCoursescourse categories:Microsoft ExcelMicrosoft OfficeLean Six SigmaFinanceProject ManagementOffice ProductivityL

What is the Averageif formula?

Toggle navigation

GoSkills Search for courses or lessons Search

  • Courses
course categories:
  • Microsoft Excel
  • Microsoft Office
  • Lean Six Sigma
  • Finance
  • Project Management
  • Office Productivity
  • Leadership & Management
  • Soft Skills
  • Development
  • Design
  • Data AnalysisView all courses

Microsoft Excel

Excel is a widely used spreadsheet program. While its most popularly known for its ability to make use of heaps of data, Excel has an infinite number of capabilities, from making lists and charts to organizing and tracking information.Learn more

Courses

  • Microsoft Excel - Basic & Advanced
  • Microsoft Excel - Basic
  • Microsoft Excel - Advanced
  • Microsoft Excel - Pivot Tables
  • Microsoft Excel - Macros and VBA
  • Microsoft Excel - Dashboards
  • Microsoft Excel - Power Query
  • Microsoft Excel - Power Pivot
  • Microsoft Excel for Mac - Basic
  • Microsoft Excel for Mac - Advanced
  • Microsoft Excel for Mac - Basic & Advanced
  • + 3 more courses

Resources

Whether you want to do XLOOKUP, COUNTIF, CONCATENATE or simply merge cells, weve got you covered with step-by-step guides to the most important tools, formulas, and functions in Excel.See resources

Certification

90% of offices use Excel. Getting Excel-certified can be highly beneficial for job seekers. Get certificates in multiple Excel courses to prove your proficiency in Excel.See certification

Microsoft Office

Learning Microsoft Office has significant benefits for your daily workflow, productivity, and career possibilities. Microsoft Office skills are indispensable across a plethora of industries and professions.Learn more

Courses

  • Microsoft Excel - Basic & Advanced
  • Microsoft Word - Basic & Advanced
  • Microsoft PowerPoint 365
  • Microsoft Outlook
  • Microsoft Teams Training
  • Microsoft OneNote Training
  • SharePoint Online
  • Microsoft Visio Training
  • Microsoft Publisher
  • Microsoft Project Fundamentals
  • Microsoft Word - Basic
  • + 13 more courses

Resources

Looking for help with Microsoft Office? Check out our free guides with tips on how to maximize the potential of apps like Word, PowerPoint, Teams, Excel and more!See resources

Lean Six Sigma

Lean Six Sigma provides a structured problem-solving methodology that can be used to address any type of problem. Being able to find and fix problems will improve your ability to perform in any position and industry.Learn more

Courses

  • Intro to Lean Six Sigma
  • Lean Six Sigma - Yellow Belt
  • Lean Six Sigma Principles - Green Belt
  • Lean Six Sigma Advanced Principles - Black Belt
  • Statistical Process Control
  • Measurement Systems Analysis
  • Hypothesis Testing
  • Failure Mode and Effects Analysis
  • Design of Experiments

Resources

Deepen your understanding of popular LSS tools and techniques, and simplify complex LSS concepts with our thorough how-to guides and resources.See resources

Certification

Lean Six Sigma certification can fast track your career and boost your pay packet. Certified Lean Six Sigma professionals are among the highest paid globally. Get trained in LSS!See certification

Finance

Whether you are in HR, Sales, IT, Admin or Support, you need to speak the language of finance. Whether discussing plans, strategy or how well your department is performing, youll be conversing in financial terms and numbers.Learn more

Courses

  • Finance for Non-Financial Professionals
  • Finance for Operations Managers
  • Finance for Project Managers
  • Finance for Operations Directors
  • Financial Modeling Basics
  • Financial Modeling Techniques
  • QuickBooks Online

Resources

From accounting software tips, to taxes and financial modelling techniques, our resource center has free guides to help you gain the finance knowledge you are looking for.See resources

Project Management

Project management jobs are in high demand with not enough skilled applicants to fill them. For qualified candidates, project management roles are available in almost every industry.Learn more

Courses

  • Project Management Basics
  • Project Management Bootcamp
  • Project Management Framework
  • Project Teams and Stakeholders
  • Project Planning
  • Project Performance and Delivery
  • Project Management for Engineers
  • Project Meetings
  • Microsoft Project Fundamentals
  • CAPM Certification Training
  • CAPM Foundations
  • + 5 more courses

Resources

Explore our resource center to find templates to help you get the job done, job interview tips, insights to tackle your biggest project management challenges, and so much more.See resources

Certification

It pays to be certified in project management. You need to complete 35 hours of PM training from an ATP to get your certification. We can help you meet that requirement.See certification

Office Productivity

How successful could you be if you were more productive? Master widely-used productivity tools like Trello, Todoist, and Google Drive to optimize your workflow and spend time doing the work that truly matters. Start getting things done!Learn more

Courses

  • Keynote for Mac
  • 1000minds Decision-Making
  • Trello
  • Google Drive & Apps
  • Todoist
  • Essential Productivity Training

Resources

Having difficulties adjusting to remote work? Need help with work-life balance? Need tips on how to use tools like Zoom or Slack effectively? Try these resources!See resources

Leadership & Management

Good leaders employ a comprehensive set of hard and soft skills to act as the oil of a well-functioning machine. While some of these traits are expressions of their inherent personality, most are learned and refined over time.Learn more

Courses

  • Leadership Training
  • Team Leadership
  • Deliver Effective Criticism
  • Listening Skills
  • Speaking Skills for Leaders

Resources

Finding yourself in need of simple tools and guidance to navigate through challenging situations as a leader? Take a look at our resources for management tips and strategies that you can implement right away.See resources

Soft Skills

Soft skills matter a lot in the workplace. Heres why. As the workforce grows more dependent on knowledge workers, companies are beginning to see the value in soft skills.Learn more

Courses

  • Business Writing Skills
  • Public Speaking
  • Body Language
  • Press Releases
  • Customer Service Training
  • Introduction to Sales
  • Introduction to Marketing
  • Communication Skills Basics
  • Remote Work Communication Skills

Resources

We want to help you succeed! Check out our resource center for help with your resume, job interviews, and your career. Let us help you put yourself ahead of the rest.See resources

Development

Learning to code could be your ticket to a lucrative and fulfilling career. High salaries, the flexibility to work from anywhere, and a healthy job outlook are just three benefits you can look forward to in this dynamic industry.Learn more

Courses

  • Introduction to HTML
  • Introduction to CSS
  • Introduction to JavaScript
  • Introduction to PHP
  • Introduction to SQL
  • Introduction to Python
  • Introduction to Data Analysis with Python
  • Django for Beginners
  • Ruby on Rails for Web Development
  • Intro to Ruby Programming
  • Python with Excel
  • + 1 more courses

Resources

Check out our resource center to find answers to common coding questions, interview tips, and step-by-step guides that will help you in your development career.See resources

Design

If you want a job where youll be working on something different every day, thriving in a fast-paced environment and loving a creative challenge, a career in graphic design may be just for you.Learn more

Courses

  • Photoshop for Beginners
  • Photoshop - Advanced
  • Illustrator - Basic
  • Illustrator - Advanced

Resources

Check out our resource guides to learn more about the graphic design tools that will help you to achieve your design dreams.See resources

Data Analysis

Become a data analysis pro with our range of expert-led courses. Whether you are a data analytics novice or a numbers whiz, our on-demand courses will help advance your skills at a pace that suits you.Learn more

Courses

  • Introduction to SQL
  • Power BI
  • Introduction to Python
  • Microsoft Excel - Power Query
  • Microsoft Excel - Power Pivot
  • Introduction to Data Analysis with Python
  • Python with Excel
  • Introduction to Tableau

Resources

From Python to Excel, or Power BI, Tableau and beyond, check out these free resources to help take your data analysis skills to the next level.See resources

  • Search for courses or lessons Search
  • Courses
  • Courses for Teams
  • LMS
  • Resources
  • Pricing
  • Redeem a couponStart free trialLog inGo to dashboard
  • Search
  • Log in
  • Start free trial
  • Go to dashboard

Microsoft Excel

8 minute read

How to Use the Excel AVERAGEIF Function

Claudia Buckley

Claudia Buckley

Join the Excel conversation on Slack

Ask a question or join the conversation for all things Excel on our Slack channel.Join Slack channel


In Excel, theres an easy way to identify and find the average of numbers that satisfy a certain criterion. With the AVERAGEIF function, Excel looks within the specified range for a single condition and then finds the arithmetic mean of the cells that meet that condition.

For more ways of calculating averages in Excel, check out our resources on calculating averages and weighted averages in Excel.

Syntax

The AVERAGEIF function has two required arguments and one optional argument.

The syntax is:=AVERAGEIF(range,criteria,[average_range])

  • Range: the location where we can expect to find cells that meet the criteria.
  • Criteria: the value or expression that Excel should look for within the range.
  • Average_range: the optional argument. This is the actual range of cells where the values to be averaged are located. If the average_range is omitted, the range is used.

Download your free practice file!

Use this free Excel AVERAGEIF file to practice along with the tutorial.Enter your email address


Text criteria (all arguments)

For example, from the list of fruit prices below, we can ask Excel to extract only the cells that say apples in column A, and find their average price from column B. In this case, all three arguments are used, since the range does not contain the numbers being used to calculate the average price.

Excel Averageif function
Excel Averageif function

Note that the text value apples is placed within double quotes.

In cases where the range and average_range size or shape are different, Excel uses a combination of the first cell in average_range, plus the size and shape of the range, to determine the actual range of cells to be averaged.

Excel Averageif function
Excel Averageif function

Though the average_range was stated as B2 to B14, only the cells in B2 to B8 were actually averaged, since those are the cells that corresponded with the A2 to A8 range.

Logical criteria

The criteria in an AVERAGEIF function may also be in the form of a logical statement, using the standard logical mathematical operators (=, >, <). However, as shown with the text examples above, the criteria argument defaults to is equal to so theres no need to enter the equal sign (=) when stating the is equal to criterion.

The following combinations of these operators are also used for other specific logical comparisons.

Operator

Meaning

>=

Greater than or equal to

<=

Less than or equal to

<>

Not equal to

An example of a less than comparison is shown below. Note that the third (optional) argument is omitted. Therefore the cells in the range are used to calculate the average.=AVERAGEIF(B2:H11, <60)

Excel Averageif function

The formula above only takes into account and finds the average of the four highlighted values  54, 49, 56, and 0. It ignores the Sick value in cell B6 since the AVERAGEIF function does not assign numeric values to text.

Cell reference criteria

We can also use a logical operator in combination with a cell reference to establish a criteria for the AVERAGEIF function. The following example finds the average of all cells within the range B2 to H11, which are less than the value in cell J1.=AVERAGEIF(B2:H11, <&J1)

Excel Averageif function - cell reference criteria

When using logical operators with cell reference criteria, the logical operator itself is enclosed within double quotation marks. This is followed by an ampersand (&) to concatenate, or join, the operator to the relevant cell reference.

As shown above, the text Sick in cell B6 was skipped when arriving at the average, so the only cells used in the calculation were C3, E3, F7, and D11.

Cells not equal to

To exclude cells that contain a certain value, the <> (is not equal to) operator should be used. For example:=AVERAGEIF(B2:H2,"<>0")

The above formula will find the average of the values within the range B2 to H2 that are not equal to zero, that is, all cells within the range except D2 below.

Excel Averageif function

By way of comparison, notice the result in the image below, where cell J2 uses the AVERAGE function instead.

Excel Averageif function

In the above example, the AVERAGE function is used in cell J2, which means all values in the range B2 to H2 are used to calculate the range. Therefore, the zero value in cell D2 affects the outcome of the average.

If not blank

The AVERAGE function ignores blank cells, so the AVERAGEIF function isnt needed if we want to get the average of cells that are not blank. To prove this point, we created a complex AVERAGEIF formula by using the logical operator <> together with empty double quotes, to represent the criterion is not equal to an empty cell in the example below.

Excel Averageif function
Excel Averageif function

The average calculated by the AVERAGEIF function in cell H2 is identical to that calculated by the AVERAGE function in cell I2. It makes sense to use the AVERAGE function for cells that are not blank  its the simplest and least complicated solution.

Multiple criteria

AVERAGEIF with OR logic

There may be times when wed like to find the average of cells within a range that satisfies any one of multiple criteria (like apples or oranges). This scenario is fairly easy to resolve by incorporating OR logic.

We can build an AVERAGEIF formula that finds the average price of apples and one that finds the average price of oranges  and simply ask Excel to average their results by nesting the two AVERAGEIF functions into the AVERAGE function, as shown below.=AVERAGE(AVERAGEIF(A2:A14,"apples",B2:B14),AVERAGEIF(A2:A14,"oranges",B2:B14))

Excel Averageif function - OR logic

AVERAGEIF with AND logic

In order to find the average of cells that satisfy multiple criteria (such as greater than x but less than y), we would use the AVERAGEIFS function, which combines AVERAGEIF with AND logic. This identifies and finds the average of cells that satisfy all of the stated criteria.

AVERAGEIF with partial matches

Sometimes the cells we want to extract from a range are alike in some ways, but they arent identical. In other words, they are only a partial match. Wildcards provide a great solution for identifying these cells, and AVERAGEIF does work with wildcards.

Wildcard

Meaning

*

Any number or string of unknown characters, or no character

?

A single unknown character

~

Precedes an asterisk or question mark to be used as a literal character

We can find the average of all text in the range A2 to A14 that ends with es by placing an asterisk before es as our criteria.=AVERAGEIF(A2:A14,"*es",B2:B14)

Excel Averageif function - partial matches

The result of the above criteria is that the average price of limes, apples, mangoes, and oranges is calculated and returned in cell D2.

Points to watch for

  • If you get a #DIV/0! error, it means that Excel wasn't able to find a value within the range that satisfies the specified criteria.
  • AVERAGEIF ignores cells within the range that contain the Boolean values TRUE or FALSE.

To learn even more ways of calculating averages in Excel, check out our resources on calculating averages and weighted averages in Excel.

Ready to be an Excel pro? You can start with the free Excel in an Hourcourse today then explore our other Excel courses including the Microsoft Excel - Basic and Advanced course.

Learn Excel for free

Start learning formulas, functions, and time-saving hacks today with this free course!Start free course

Loved this? Subscribe, and join 369,312 others.

Get our latest content before everyone else. Unsubscribe whenever.Your email address

Join the Excel conversation on Slack

Ask a question or join the conversation for all things Excel on our Slack channel.Join Slack channel

Claudia Buckley

Claudia Buckley

Claudia is a content creator and business skills instructor at GoSkills. If she's not at work, she's probably tackling a genealogy project.

Facebook Twitter LinkedIn WhatsApp Pocket Email

Excel Challenge 19

Recommended

Excel Challenge 19

What Excel functions do you use when multiple combinations could lead to different outcomes? Share it with us by working on our latest Excel challenge!

20 Excel Date Functions - Explained!

Recommended

20 Excel Date Functions - Explained!

There are more than 20 date formulas in Excel to help simplify common operations involving dates. Here is a list of some top Excel date functions.

Excel Challenge 18

Recommended

Excel Challenge 18

How do you use two lookup values to return a single value in Excel? Join this month's Excel challenge to find out!GoSkills courses

  • GoSkills Unlimited
  • Microsoft Excel
  • Microsoft Office
  • Lean Six Sigma
  • Finance
  • Project Management
  • Office ProductivityCompany
  • About us
  • Careers
  • Courses for teams
  • Learning management system
  • Accreditations & awards
  • FAQ
  • Terms & PrivacyDiscover
  • Resources
  • Scholarship
  • Gifts
  • Reviews
  • Redeem a code
  • Verify a certificate
  • Site mapContact

USA/Canada: USA/CA: +16508227732
Email:  Media:

Facebook Twitter LinkedIn Instagram

  • Send us a messageAbout

GoSkills - Skills for career advancement

Advance your career with GoSkills! We help you learn essential business skills to reach your full potential. Learn effectively via bite-sized video tutorials taught by award-winning instructors.
Thank you for choosing to learn with us.Newsletter    Your email address

Dont miss out on our best deals! Sign up for our newsletter to get the latest news and specials delivered direct to your inbox.Back to the top

© 2022 GoSkills Ltd. Skills for career advancement

Video liên quan