AVERAGEIFS Excel




In this tutorial, you will learn about the AVERAGEIFS function in excel. AVERAGEIFS excel is useful to do an average calculation with more than one specific criteria for the numbers that want to be included in the calculation.


Why do We Need to Learn About AVERAGEIFS Function in Excel?


In doing numeric data processing on a spreadsheet, often we need to do an average calculation from some numbers that we have. Sometimes there are some specific requirements that need to be considered to make just particular numeric data that will be included in the calculation process. This is quite difficult to do if there are many data entries in our spreadsheet with the data that meet the criteria are spread in their position and they are not in one particular range.

As an example of this in work, imagine if we want to do demand prediction analysis to a product on a particular day and region by using demand history data of all products that we have. To do the analysis, we want to calculate the average of demand data in the spreadsheet with various criteria like the product, the particular day that we want to analyze, and the region as per our requirements.

In the analysis process, usually the more entries in the calculation, the better the prediction result is. But if the data that fit all of our requirements are spread in the spreadsheet in terms of their position, then it will be very troublesome to look on the entries one by one and input the numbers from the entries that satisfy all of our requirements to the manual formula that we write or to the AVERAGE formula in excel to get the result as we need in our analysis.

To help in making the calculation process with the need like that easier, then we can use the AVERAGEIFS excel function. AVERAGEIFS function in excel can do the filtering to the data entries that meet all of your requirements to then take the numbers to do the average calculation process that you need. AVERAGEIFS function in excel is very useful to be understood and learned if you often do the average calculation in a spreadsheet when you do your data processing.


What is the AVERAGEIFS Excel?


AVERAGEIFS excel is a function with the usage objective to get the average result of the numbers from all the data entries that meet a set of criteria. AVERAGEIFS excel formula is one of the variants of the AVERAGE formula besides AVERAGEIF.

Generally, the inputs in AVERAGEIFS excel formula can be explained as follows:


=AVERAGEIFS(average_range, criteria_range1, criteria1, …)


Notes:
  • average_range = the row/column where the numbers from the data entries that we want to calculate the average from are in
  • criteria_range1 = the row/column where the data that you want to evaluate with the first criteria are in
  • criteria1 = your first criteria
  • … = the rows/columns where the next data evaluation from the entries will be done and their criteria pair as needed



How to Use the AVERAGEIFS Function in Excel?


The following will explain how to write the AVERAGEIFS excel formula. AVERAGEIFS excel formula needs the inputs of the row/column where the numbers to be calculated the average from your entries are, the row/column where the data to be evaluated with your criteria are, and the criteria. You need to input a minimum of one data row/column to be evaluated with its criteria also. The row/column that consists of the numbers to be averaged and the row/column with the data to be evaluated with the criteria must be parallel in their position to one another so AVERAGEIFS function in excel can function properly.


Using AVERAGEIFS Excel

  1. Type equal sign ( = ) in the cell where you want to put the result

    SUMIFS Function in Excel - Screenshot of Step 1

  2. Type AVERAGEIFS (can be with large and small letters) and open bracket sign after =

    SUMIFS Function in Excel - Screenshot of Step 2

  3. Drag cursor on the row/column where the numbers which you want to process based on your criteria are in and type comma sign ( , )

    SUMIFS Function in Excel - Screenshot of Step 3

  4. Drag cursor on the row/column where the data that you want to evaluate with your first criteria is and type comma sign. The row/column of the data that you want to evaluate with the criteria must be inline vertically/horizontally with the row/column with the numbers that you want to calculate and should have the same number of cells

    SUMIFS Function in Excel - Screenshot of Step 4

  5. Type your first criteria then type comma sign. The criteria must be inside quote sign ( “” ) if it is in the form of text or more than/less than criteria for numbers

    SUMIFS Function in Excel - Screenshot of Step 5

  6. Type a comma sign after the first criteria is inputted if there is/are still other requirement(s) to be inputted and redo steps 4-5

    SUMIFS Function in Excel - Screenshot of Step 6

  7. Type close bracket sign after all the requirements have been inputted.

    SUMIFS Function in Excel - Screenshot of Step 7

  8. Press Enter
  9. The process is done!

    SUMIFS Function in Excel - Screenshot of Step 9



Exercise


After you have understood the details of how to use AVERAGEIFS function in excel, now is the time you sharpen your understanding through the following exercise!

Download the exercise file and answer all of the questions! Download the answer key file if you are done and want to check your answers or if you are confused on how to answer the questions and need a clue!

Link for the exercise file:
Download here

Questions

  1. What is the result of AVERAGEIFS function in excel for the sales quantity from A in region 1?
  2. What is the result of AVERAGEIFS function in excel for the sales quantity from C with the indirect selling method?
  3. What is the result of AVERAGEIFS function in excel for the sales quantity from A in region 2 with the direct selling method?

Link for the answer key file:
Download here


Additional Notes


If you want to do normal average calculation or just with one criteria, then you can learn them in the other parts of Compute Expert tutorials!



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on


Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation



Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel



Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2021 Compute Expert