AVERAGEIFS is a perfect excel function when you want to calculate average using more than one criteria.
It can take more than one value as criteria and average only those cells which meet that.
Let’s say you have 10 cells and you have 3 criteria.
Now, out of those 10 cells, it will only average only those cells which meet all 3 criteria you have mentioned.
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
average_range A range of cells for which you want to calculate average.
criteria_range1 A range of cells from which you want to test the condition.
criteria1 A condition which you want to mention
[criteria_range2] Second range of cells from which you want to test the condition.
[criteria1] A condition which you want to mention
More Information on Excel AVERAGEIFS Function
- If cells in average range are blank/text value then it will return an error.
- If cells in criteria range are blank/text value then it will return an error.
- And , if any cell from criteria range or average range has TRUE or FALSE it will consider it as 1 and 0 respectively.
- You can also use logical operators & wildcard characters in criteria.
- The size and shape of average range and each criterion range should be same.
- It will return #DIV/0! if there is no match found in criteria range.
- Make sure to enclosed non-numeric values in double quotation marks.
Note: The AVERAGEIFS function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution.
In below example, I have used the averageifs function to calculate the average of quantity sold by salesman “Peter” and for the product “B”.
I have inserted criteria directly into the function. And, we have two entries of Peter’s sale of product B.
In below example, I have used AVERAGEIFS with an asterisk to calculate the average of price of fruits whose quantity is greater than 20 units and has B in its name.
In below data table, We have two fruits who meet these criteria.
To learn more about Excel AVERAGEIF Function you can check Microsoft’s Help Section.
And, if you have a unique idea to use averageif function, I would love to hear from you.