{"id":686,"date":"2019-04-18T13:20:54","date_gmt":"2019-04-18T10:20:54","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=686"},"modified":"2019-04-18T13:39:43","modified_gmt":"2019-04-18T10:39:43","slug":"the-averageifs-function","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/the-averageifs-function\/","title":{"rendered":"The AVERAGEIFS Function"},"content":{"rendered":"\n

The AVERAGEIFS<\/strong> function returns the arithmetic mean (average) of all the cells that meet all its multiple criteria. Even though its logical elements, it is part of the Statistical functions of Excel.<\/p>\n\n\n\n

We have prepared a tutorial on the AVERAGEIFS<\/a> <\/strong>function to explain it in a bit more detail.<\/p>\n\n\n\n

\n\nThe syntax of the function is the following:\n\n<\/p>\n\n\n\n

AVERAGEIFS<\/strong>(Average_range<\/strong>, Criteria_range1<\/strong>, Criteria1<\/strong>, [Criteria_range2<\/strong>, Criteria2<\/strong>, ..<\/strong>.]) <\/p>Average_range<\/strong>: The cell range for which we want to calculate the arithmetic mean.
Criteria_range1<\/strong>, Criteria_range2<\/strong>, … : Criteria_range1 is required. The rest (up to 127) are optional. They are the range of cells that have to meet certain criteria.
Criteria1<\/strong>, Criteria2<\/strong>, … : Same as above only the first is required. The rest (up to 127) are optional. You cannot enter Criteria without a Criteria_range and vice versa. They contain the criterion (expression, number, reference…) that the respective criterion_range has to meet.<\/cite><\/blockquote>\n\n\n\n

<\/div>\n\n\n\n

If Average_range <\/strong>is blank or text, AVERAGEIFS returns the #DIV0!<\/strong> error.<\/p>\n\n\n\n

If a cell in a criteria range is empty, AVERAGEIFS considers it a zero.<\/p>\n\n\n\n

TRUE is considered 1 and FALSE 0.<\/p>\n\n\n\n

Each Criteria_range <\/strong>must be the same size and shape as Average_range<\/strong>.<\/p>\n\n\n\n

If cells in Average_range <\/strong>are not numbers, AVERAGEIFS returns the #DIV0!<\/strong> error.<\/p>\n\n\n\n

If there aren’t any cells that meet all the criteria, AVERAGEIFS returns the #DIV\/0!<\/strong> error.<\/p>\n\n\n\n

You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.<\/p>\n\n\n\n

The AVERAGEIFS function is used to measure Central Tendency<\/a><\/strong>.<\/p>\n\n\n\n\n

<\/div>\n\n\n\n

Click on the button to practice using this function, with the help of our Online Assessment Tool<\/strong>:<\/p>\n\n\n

Practice<\/a><\/p>\n\n\n\n

<\/div>\n\n\n\n\n

Here is an example of how to use the AVERAGEIFS function:<\/strong><\/p>\n\n\n\n

Use the proper formula in the cell I2, to calculate the average of the Total of Sales, from the western district, with greater than 100 units sold.<\/p>\n\n\n\n

\n