If a range has no value or text, it will return #DIV/0!.

Cells in the range which contain TRUE or FALSE are ignored.

The size of average_range and range should be same.

Text, logical operators should be enclosed in double quotes.

If you skip specifying the average range, it will return the average of cells from range (If the range has numeric values). And, if the range has other than numeric values it will return #DIV/0!.

Note: The AVERAGEIF function calculates 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 AVERAGEIF to calculate the average of product C. I have directly inserted criteria into the function by using double quotes.

We have two entries of product C in which we have quantities 12 and 8 respectively. AVERAGEIF Function checks for the product C in column A and average corresponding values from column B.

In the below example, I have used an asterisk to calculate the average quantity of fruits who have alphabet A in their name. I have two fruits in above list who have Alphabet A in their name.

AVERAGEIF Function verifies the condition from the column A and calculates the average of corresponding values from column B.

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 it, I would love to hear from you.