How to use Excel AVERAGEIFS Function

    how to use excel averageifs function

    Quick Intro

    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.

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

    Example For Excel AVERAGEIFS Function

    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.

    Example-For-Excel-AVERAGEIFS Function With Wildcards

    Sample File

    download sample file to learn more about this tips

    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.

    There is also you have AVERAGEIF function which is highly useful. Apart from this, I have a list of excel functions and some real life formulas examples.