How to use AVERAGEIFS function on Excel

AVERAGE is the basic Excel function, used to calculate the average of the values ​​in the data table. And when using the advanced AVERAGE function, users can combine more to calculate the various contents such as, AVERAGEIF function to average with 1 condition, or DAVERAGE function. In the following article, we will guide you how to use the AVERAGEIFS function, calculating the average value combining various conditions in the data table.

1. How to combine Sumif and Vlookup functions in Excel
2. How to use Lookup function in Excel
3. How to use Vlookup function in Excel

Instructions for using the AVERAGEIFS Excel function

AVERAGEIFS function on Excel has a function formula is AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2], .) .

Inside:

1. average_range: The data area that needs to calculate the average value includes the reference number or name or array containing the number, which is the required parameter.
2. criteria_range1, criteria_range2: The condition containing the total condition, criteria_range1 is the required parameter, the remaining criteria_range options up to 127 arguments, are required parameters.
3. criteria1, criteria2, .: As a condition to average plus, riteria1 is a required parameter, other criteria optionally contain a maximum of 127 conditions.

Note when using functions:

1. If the value of the argument contains a logical value or a blank cell, that value will be ignored.
2. If the range is empty or textual, the function returns the # DIV / 0 error value.
3. If the cells in Criteria_range are empty, the default function is 0.
4. If the value in the data cell is logical value = True, it is considered as 1, if False value is considered 0.
5. If the values ​​in average_range cannot be converted to numbers then the error # DIV / 0 is reported.
6. If no value meets all conditions, the error function # DIV / 0!
7. Can use alternate characters like *.?. in terms of average calculation.

We will take the example with the data table as shown below.

Example 1 : Calculate the average of employees' wages greater than 3,000,000 and less than 7,000,000.

Step 1:

You enter the formula = AVERAGEIFS (D2: D6, D2: D6, "> 3000000", D2: D6, "<7000000") .

Where D2: D6 is the data column to be calculated and D2: D6, "> 3000000", D2: D6, "<7000000" are conditions in that data area.

Step 2:

It will then display the average result plus the data based on the established condition. The calculated amount is 5,433,333.

Example 2 : Calculating the average of bonuses of male employees with a salary of 250,000.

Step 1:

Also in the input box, users enter the formula to calculate the value = AVERAGEIFS (E2: E6, E2: E6, "250000", C2: C6, "Nam") .

Where E2: E6 is the area to calculate the value, E2: E6, "250000" is the first condition for the value in that region. C2: C6, "Male" is the second condition with another data area.

Step 2:

When pressing Enter, the result is 250,000 as shown below.

Example 3 : Calculate the average of the salaries of male employees larger than 200,000 and wages greater than 4,500,000.

Step 1:

In the input box we enter the formula = = AVERAGEIFS (E2: E6, E2: E6, "> 200000", C2: C6, "Male", D2: D6, "> 4500000") .

Where E2: E6 is the data area to calculate the average value, E2: E6, "> 200000" is the first condition in the data area E2: E6, C2: C6, "Male" is the second condition at Data area C2: C6, D2: D6, "> 4500000") is the 3rd condition in the data area D2: D6.

Step 2:

Press Enter and the result will be 375,000 as shown below. Because only 2 male employees meet all 3 conditions in the request.

Example 4 : Calculate the average salary of female employees with wages greater than 7,500,000.

Step 1:

Also in the box that displays the results, the user enters the formula for calculating AVERAGEIFS = = AVERAGEIFS (D2: D6, D2: D6, "> 7500000", C2: C6, "Female") and then press Enter.

Step 2:

The result will indicate error # DIV0! Since only one condition is satisfied in the formula, no cell satisfies all of the two conditions in the formula.

Above is how to apply the AVERAGEIFS function in Excel to calculate the average value with many different conditions. In general, the usage is very simple, you need to enter the area to calculate the value first, then the conditions on each different data area.

See more:

1. Summary of trigonometric functions in Excel
2. How to automatically display names when entering code in Excel
3. Calculation of age in Excel

I wish you all success!