• Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us
Excel

How to use the AVERAGEIFS function to calculate the average based on multiple criteria in Excel

0 Comments

How to use the AVERAGEIFS function to calculate the average based on multiple criteria in Excel: In Excel, the AVERAGEIFS function is used to calculate the average value of numbers in a range of cells that meet multiple conditions at the same time. You may wonder how AVERAGEIFS differs from AVERAGEIF. It’s simple: AVERAGEIF can only calculate the average value of cells that meet a single condition. If you have more than one condition, you need to combine it with another function or, more simply, use the AVERAGEIFS function. The following article will guide you on how to use the AVERAGEIFS function in Excel.

1, AVERAGEIFS function structure

Function syntax: =AVERAGEIFS(average_range; criteria_range1; criteria1; [criteria_range2; criteria2]; …)
Where:

  • average_range: Required argument, the range to calculate the average value. It can include numbers, names, arrays, or references that contain numbers.
  • criteria_range1, criteria_range2, …: criteria_range1 is a required argument, and the next criteria_range is an optional argument. These are the ranges of conditions.
  • criteria1, criteria2, …: criteria1 is a required argument, and the next criteria are optional arguments. These are the conditions.

Note:

  • If a cell in the criteria range is empty, the AVERAGEIFS function will default that cell to 0.
  • If the average_range is empty or contains text, the AVERAGEIFS function will return the #DIV0! error value.
  • Each criteria_range must have the same size and shape.
  • If no cells meet all the criteria, AVERAGEIFS will return the #DIV/0! error value.
  • Wildcard characters, question mark (?) and asterisk (*) can be used in conditions to substitute for characters.

2. How to use the AVERAGEIFS function

Let’s consider an example of a table showing math and literature scores of students as shown below.

How to use the AVERAGEIFS function to calculate the average based on multiple criteria in
a. Calculate the average literature score of female students with math scores greater than 5.
=AVERAGEIFS(E2:E11;C2:C11;”Female”;D2:D11;”>5″)

Where:

  • E2:E11 is the range of literature scores to calculate the average.
  • C2:C11 is the range of gender conditions.
  • “Female” is the condition for gender being female.
  • D2:D11 is the range of math score conditions.
  • “>5” is the condition for math scores greater than 5.

How to use the AVERAGEIFS function to calculate the average based on multiple criteria inb. Calculate the average math score of female students with names starting with “H”.
The formula would be: =AVERAGEIFS(D2:D11;C2:C11;”Female”;B2:B11;”H*”)

Where:

  • D2:D11 is the range of math scores to calculate the average.
  • C2:C11 is the range of gender conditions.
  • “Female” is the condition for gender being female.
  • B2:B11 is the range of name conditions.
  • “H*” is the condition for names starting with “H”. The asterisk (*) is used as a wildcard for characters.
How to use the AVERAGEIFS function to calculate the average based on multiple criteria in Excel

How to use the AVERAGEIFS function to calculate the average based on multiple criteria in Excel

That’s it! The above article has guided you on how to use the AVERAGEIFS function in Excel. Good luck!

Rate this post
30
270 Views
How to use the FV function to calculate the future value of an investment in ExcelPrevHow to use the FV function to calculate the future value of an investment in ExcelMay 18, 2023
How to use the DMAX function to find the maximum value that satisfies a given condition in ExcelMay 18, 2023How to use the DMAX function to find the maximum value that satisfies a given condition in ExcelNext

Leave a Reply Cancel reply

You must be logged in to post a comment.

Buy Windows 11 Professional MS Products CD Key
Buy Office 2021 Professional Plus Key Global For 5 PC
Top rated products
  • Kaspersky Internet Security 2021 1 year 5 devices key Global Kaspersky Internet Security 2021 1 year 5 devices key Global
    Rated 5.00 out of 5
    $45.00
  • Windows Server 2022 Datacenter Key Global Windows Server 2022 Datacenter Key Global
    Rated 5.00 out of 5
    $7.00
  • Kaspersky Small Office Security 10 PCs + 10 Mobiles + 1 Server 1 Year Kaspersky Small Office Security 10 PCs + 10 Mobiles + 1 Server 1 Year
    Rated 5.00 out of 5
    $164.50
  • Avast Ultimate Suite 2021 1 Year 10 Devices Global Avast Ultimate Suite 2021 1 Year 10 Devices Global
    Rated 5.00 out of 5
    $54.00
  • Windows 11 Pro Key Global Windows 11 Pro Key Global
    Rated 5.00 out of 5
    $6.00
Products
  • Visio Professional 2024 Visio Professional 2024 $33.00
  • Project Standard 2021 Microsoft Project Standard 2021 - 1 PC $13.00
  • Project Professional 2024 Project Professional 2024 $33.00
  • Project Professional 2021 Microsoft Project Professional 2021 - 1 PC $13.00
  • Windows Server2008 R2 Remote Desktop Services device connections (20)cal Windows Server2008 R2 Remote Desktop Services device connections (20)cal $29.00
  • Microsoft Project 2019 Professional Key Global Microsoft Project 2019 Professional - 5 PC
    Rated 4.97 out of 5
    $12.00
  • Windows 10 Enterprise Key Global Windows 10 Enterprise Key Global
    Rated 4.82 out of 5
    $9.00
  • Kaspersky Small Office Security 10 PCs + 10 Mobiles + 1 Server 1 Year Kaspersky Small Office Security 10 PCs + 10 Mobiles + 1 Server 1 Year
    Rated 5.00 out of 5
    $164.50
  • Microsoft Visio 2016 professional CD Key Global Microsoft Visio 2016 professional CD Key Global
    Rated 4.94 out of 5
    $28.50
  • Avast Ultimate Suite 2021 1 Year 10 Devices Global Avast Ultimate Suite 2021 1 Year 10 Devices Global
    Rated 5.00 out of 5
    $54.00
Product categories
  • Anti Virus
  • Microsoft Office
  • Microsoft Project
  • Microsoft Visio
  • Microsoft Visual
  • Microsoft Windows
  • Other Software
  • Uncategorized

Buffcom.net always brings the best digital products and services to you. Specializing in Office Software and online marketing services

BIG SALE 50% IN MAY

Microsoft Office
Microsoft Windows
Anti-Virus
Contact Us

Visit Us:

125 Division St, New York, NY 10002, USA

Mail Us:

buffcom.net@gmail.com

TERMS & CONDITIONS | PAYMENT GUIDE  | SHIPPING POLICY  | REFUND POLICY

Copyright © 2019 buffcom.net  All Rights Reserved.