AVERAGEIFS function - The function returns the average of the arguments that meet multiple conditions in Excel

The following article introduces you to the AVERAGEIFS function - one of the functions in the statistical function group is very popular in Excel.

AVERAGEIFS function - The function returns the average of the arguments that meet multiple conditions in Excel Picture 1

Description: The function returns the average of the arguments that meet multiple conditions.

Syntax: AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2], .)

Inside:

- average_range: The data area to calculate the average value including number or name or reference array containing numbers, is a required parameter.
- criteria_range1, criteria_range2: Range containing conditionals to be summed, of which criteria_range1 is a required parameter, the remaining criteria_range are optional and contain up to 127 arguments, which are required parameters.

- criteria1, criteria2, .: The condition to calculate the average, of which criteria1 is a required parameter, other criteria are optional parameters, containing up to 127 conditions.

Attention:

- If the value of the argument contains a logical value or a blank cell -> that value is ignored.

- If the range is blank or text -> the function returns the # DIV / 0 error value

- If a cell in the criterion is blank -> the function considers it as a value of 0.

- If the value in the data box is a logical value = True -> is considered 1, the value of False is considered to be 0.

- If the values ​​in average_range cannot be converted to numbers -> the function returns the # DIV / 0 error value

- If no value meets all of the conditions -> the function returns the # DIV / 0 error value !

- Alternative characters such as *,? Can be used in the condition of averaging.

For example:

Calculate the average of the values ​​with the condition in the following data table:

AVERAGEIFS function - The function returns the average of the arguments that meet multiple conditions in Excel Picture 2

1. Calculate the average of the profits of consumer goods groups with profits greater than 200.

- In the cell to calculate enter the formula : = AVERAGEIFS (C6: C10, B6: B10, "= * consumption *", C6: C10, "> 200")

AVERAGEIFS function - The function returns the average of the arguments that meet multiple conditions in Excel Picture 3

- Press Enter -> the average value of consumer goods items with profits greater than 200 are:

AVERAGEIFS function - The function returns the average of the arguments that meet multiple conditions in Excel Picture 4

2. Calculate the average plus profit of consumer goods except for the Chinese group.

- In the cell to calculate enter the formula: = AVERAGEIFS (C6: C10, B6: B10, "= * consumption *", B6: B10, "<> * China *")

AVERAGEIFS function - The function returns the average of the arguments that meet multiple conditions in Excel Picture 5

- Press Enter -> Average value of profit of consumer goods except Chinese goods is:

AVERAGEIFS function - The function returns the average of the arguments that meet multiple conditions in Excel Picture 6

Above are instructions and specific examples when using the AVERAGEIFS function in Excel.

Good luck!