The AVERAGEIFS function in Google Sheets is a powerful tool designed to compute the average of a set of data, conditioned by multiple criteria. This function enables users to analyze their data more effectively by providing refined insights based on various parameters, contributing to more informed decision-making.

Syntax

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

• average_range: The range of cells to calculate the average from.
• criteria_range1: The first range to evaluate against the criteria.
• criteria1: The condition that must be met in the first criteria range.
• [criteria_range2, criteria2, …]: Additional ranges and criteria (optional).

Example #1

AVERAGEIFS(B2:B10, A2:A10, "Sales", C2:C10, "North")

This function calculates the average of values in the range B2:B10 where the corresponding values in A2:A10 are ‘Sales’ and those in C2:C10 are located in ‘North’. For example, if the average of qualifying numbers is 500, the result will be 500.

Example #2

AVERAGEIFS(D2:D10, E2:E10, ">=50", F2:F10, "<100")

This function computes the average of the range D2:D10 where the values in E2:E10 are greater than or equal to 50 and those in F2:F10 are less than 100. If the result of this calculation is 75, then 75 is returned.

Example #3

AVERAGEIFS(G2:G10, H2:H10, "Orange", I2:I10, "Yes")

Here, the average is calculated for the range G2:G10 based on the conditions that H2:H10 must equal 'Orange' and I2:I10 must be 'Yes'. If the average meeting these criteria results in 30, the output is 30.

Error handling

• DIV/0!: This error occurs when there are no data points that meet the specified criteria, resulting in an attempt to divide by zero.
• VALUE!: Indicates that one of the arguments in the function is of the wrong type, often arising from non-numeric data in cell ranges.
• REF!: Occurs when a reference to a cell or range is not valid, possibly due to the deletion of rows or columns.
• NAME?: This signals that the function name is spelled incorrectly or is not recognized by Google Sheets.

Conclusion

In summary, the AVERAGEIFS function serves as a critical analytical tool within Google Sheets, allowing users to accurately calculate averages based on multiple conditions. By mastering this function, you can enhance your data analysis capabilities, making your insights more precise and actionable.