**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.

## Syntax

**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.**

## Examples

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.

## Sample File

## What’s Next?

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.

There are also you have AVERAGEIF function which you can use if you have only one criteria to specify. Apart from this, I have a list of excel functions and some real life formulas examples.