AVERAGEIFS is a perfect excel function when you want to calculate an 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.

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.

Examples

In the 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 the 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.

Sample File

What’s Next?

To learn more about Excel AVERAGEIFS Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.