Table Of Contents
Excel AVERAGEIFS Function
AVERAGEIFS function in Excel is used to find the average from the target range of cells when more than one condition is met. For a single condition, we used AVERAGEIF, but for multiple conditions, we used AVERAGEIFS. It can take arguments where the first argument is the range column in which the average needs to be calculated. The rest are the criteria range and followed by criteria.
For example, we need to know the average score of female students in Physics. To calculate the average score based on the condition (female students), we can apply the AVERAGEIF function. In addition, we could employ the AVERAGEIFS function in Excel if there were more than one criterion.
Now, look at the syntax of the Excel AVERAGEIFS function, then we will move to the example section.
- Average Range: We need to take the average for the range of cells.
- Criteria Range 1: We need to take the average out. So, in this argument, we need to select that column.
- Criteria1: In Criteria Range 1, we need to consider the average for which value.
- Criteria Range 2: This is the second criteria range to take the average out, so we need to select that second column in this argument.
- Criteria2: In Criteria Range 2, we need to consider the average for which value.
Note: Logical value TRUE or FALSE is considered as 1 and 0. Even zero value is considered for average.
Examples
Example #1
Let us look at the example.
- Assume you want to take out the average sales of the product in a particular region in a specific month. Below is the data to demonstrate.
We need to take the region “North” average for the month “March in the above data.” So here, we need to look at two criteria to take the average out. - Open the AVERAGEIFS function now.
- Since we need to take the average Sales, select the “Sales” column for the Average Range argument.
- The next argument is Criteria Range 1. So, select the 9*Region column as the reference.
- We need the average of the East region for this criteria range, so mention Criteria 1 as East.
- The next argument is Criteria Range 2. So, select the Month column as the reference.
- Criteria 2 is derived from Criteria Range 2, for which month we need to take the average out. For example, we need the average for the Mar month, so we must mention the same in Criteria 2.
- Now, we need to press Enter to get the result.
So, the average sales for the region “East” for the month “Mar” is 2067.67.
Example #2
We can also use operator symbols in the criteria part of the Excel AVERAGEIFS function. For example, look at the below data.
We need to take the average sale between two dates from this data. So, first, we need to take the average sales between 27-Apr-2019 to 26-May-2019, then the average sales between 26-May-2019 to 17-Jun-2019.
We should supply these criteria with the logical operator in Excel symbols.
- Let us open the AVERAGEIFS function in the D4 cell now.
- We must select the Average Range as the “Sales” column.
- Next, we must select Criteria Range 1 as a "Date" column.
- Criteria 1 should be greater than 27-Apr-2019. So, first, we must mention the greater than symbol in double quotes, then combine this with the D3 cell.
- For Criteria Range 2, we must select the "Date" column.
- For Criteria 2, we need the average sales for the date lesser than 26-May -2019. So, enter less than symbols in double quotes and combine it with the E3 cell.
- Press the "Enter" key; we will get the average.
- So, the average sales between 27-Apr-2019 to 26-May-2019 is 4620.8. Similarly, we need the average sales between 25-May-2019 to 18-Jun-2019. Copy and paste the formula from the D4 cell to the D8 cell.
We got the average sales as 3770.21. So, from this, we can conclude that average sales are more in the first period- 27-Apr-2019 to 26-May-2019.
We can use this function to take the average of numbers based on multiple criteria.
