How to use the AVERAGEIF formula in Google Sheets

The AVERAGEIF formula in Google Sheets is similar to the AVERAGE formula, but there’s a key difference. Like the name suggests, it only includes a value in the average calculation if it meets certain criteria.

In effect, this is the AVERAGE and IF formulas combined into a convenient package. So it’s a pretty useful tool that will save you time and get your spreadsheet working more efficiently.

In this article we will see how this function works, we will also explore some of its basic applications.

Syntax of AVERAGEIF in Google Sheets

AVERAGEIF(criteria_range, criterion, [average_range])

  • criteria_range – The range of cells to check against the criterion.
  • criterion – The condition specified to include values from criteria_range in the average.
  • average_range – An optional range of cells to apply the average. This is useful if you want to test a criterion in one column but want the average from another column. If we do not specify this parameter, the formula averages criteria_range


How to use the AVERAGEIF formula

Below is a sample of nutritional information from a set of foods and a set of AVERAGEIF formulas applied to it.

AVERAGEIF formula 1

The spreadsheet has sample data in A1:G10 and AVERAGEIF formulas in H1:J7. Notice that the first two formulas have text comparisons while the last four have number-based comparisons.

Let’s break down the first formula:

  • Criteria_range points to A2:A10 which has “Category” information.
  • Criterion evaluates if the text in these cells is “Vegetables”
  • Average_range is C2:C10 and will calculate average “Energy”. 
  • This AVERAGEIF formula checks foods that are in the “Vegetables” category and finds their average energy they have.

The formula in row 5 is a little different. The last parameter is not specified so criteria checking and value averaging are both carried out in the same cell range (D2:D10).

AVERAGEIF returns the average of values satisfying a single criterion, if you want to find the average of values that meet multiple criteria then AVERAGEIFS is what you are looking for. Learn how to use the AVERAGEIFS formula in Google Sheets.

Automate your work in Google Sheets

Do you routinely apply the same formulas to datasets for analysis and reporting? Try connecting Google Sheets to move and filter data between one file and another and automate some of your data processing work.

Ready to streamline your spreadsheet data?

You may also like…