However, what if we want to include multiple criteria in calculating the average value of a set of data?

For such cases, you can use the AVERAGEIFS function. The AVERAGEIFS function is used to calculate the average value of a set of data that meets certain criteria, terms or conditions (multi-criteria).

We can enter up to 127 criteria or conditions when calculating the average of a data set by using this function.

An example of implementing this function in real life, for example, you want to calculate the average sales of stationery based on the type of stationery and the company that produces it.

Calculating Multi-Criteria Averages with AVERAGEIFS

The AVERAGEIFS function syntax is:

AVERAGEIFS(Range_Average; Range_Criteria1; Criteria1; [Range_Criteria2; Criteria2]; ...)

The following is a description of each of the arguments above:

* Range_Average :Ã‚ Is a set of data or values for which the average value will be calculated, which can be a number, cell, range, named range, array, or range data reference.

* Range_Kriteria1:Ã‚ Is the data range that will be adjusted to the first criterion applied.

* Criteria1 : Is the first condition or criterion of the data source in Range_Kriteria1.

* Range_Kriteria2: Represents the range of data to which criteria or conditions will be defined.

* Criteria2: Represents the terms or criteria of the data source in Range_Kriteria2.

Examples of Using AVERAGEIFS

Consider an example of using the AVERAGEIFS function below:

how to calculate average with multi criteria in Excel with AVERAGEIFS how to calculate average with multi criteria in Excel with AVERAGEIFS

In the example image above, there are several formulas, namely:

Formula 1:

=AVERAGEIFS(D2:D10,B2:B10,"Study Table",E2:E10,"PT.B")

The above formula is used to calculate the average number of "Study Tables" produced by PT. B and the average number is 57.5.

Formula 2:

=AVERAGEIFS(D2:D10,E2:E10,"PT.A",D2:D10,">50")

The formula above is used to calculate the average number of furniture products produced by PT. A which only amounts to more than 50 and the average number is 90.

Formula 3:

=AVERAGEIFS(D2:D10,B2:B10,"Table*",D2:D10,">60")

The formula above is used to calculate the average number of furniture products that begin with the word "table", which only amounts to more than 60 and the average number is 165.

In this formula, wildcard characters are used in the form of a question mark (?) and an asterisk (*) as in the example above. Where, the question mark (?) represents any single character while the asterisk (*) represents multiple characters.

Notes:

* Data that is TRUE is considered 1 while FALSE is considered 0.

* If the Range_Average argument contains an empty value or text then AVERAGEIFS will generate an error message "#DIV0!".

* If there are empty cells in Range_Criteria then AVERAGEIFS will consider them as 0.

* If in Range_Kriteria there are cells that cannot be translated into numbers then AVERAGEIFS generates an error message “#DIV0!“.

* Note that in the AVERAGEIFS function, each Data Range and Criteria must be the same size and shape.

That's all for the discussion this time about how to calculate the average with multi-criteria in Excel with the AVERAGEIFS function to calculate the average value that meets certain conditions or criteria (multi-criteria). Hopefully useful and good luck. Thank you!

Article Resources

* AVERAGEIFS function – Office Support | https://bit.ly/2SCWsof

* AVERAGEIFS Formula – Excel Class | https://bit.ly/2Z9dTiO