AVERAGEIF


Explanation of the AVERAGEIF function

The AVERAGEIF function is asking for 3 arguments as shown below:

  1. range
  2. criteria
  3. [average_range]

Example data used to explain how the AVERAGEIF function works

  • 3 different colors
  • each color has 3 different sizes
  • 2 locations (color coded to easily distinguish them apart)
  • Quantity sold at each of the 2 locations

AVERAGEIF arguments

1. range (1st argument)

The AVERAGEIF function will use the range (or ‘criteria range’) that you provide to find a match against your criteria. The range that you provide can be either an entire column or a range.

2. criteria (2nd argument)

This is the criteria that you want to look for in the range (or criteria range). This will determine which of the numbers from the [average_range] get averaged into the final result.

  • if any values in the range you provide do not match your criteria –> the corresponding values from the [average_range] are ignored
  • if the values in the range you provide match your criteria –> the corresponding values from the [average_range] get averaged

3. [average_range] (3rd argument)

The [average_range] is what the AVERAGEIF function will use to average the numbers whenever it finds a match for your criteria.

How does the AVERAGEIF function work?

You should use the AVERAGEIF function when you need the average of a set of numbers only for a certain criteria. When your criteria is found within the range you provide, it will average the corresponding numbers in your [average_range].

The AVERAGEIF function starts with the 1st value in the range (or criteria range) that you provide and checks to see if it matches the criteria you provided.

  • if yes, it includes the value from the [average_range] into the final average
  • if not, it ignores the value from the [average_range]

It does this evaluation for every value within your range or (criteria range) and averages all of the values from the [average_range] where it found a match for your criteria.

Keep in mind the AVERAGEIF function syntax when reading the Formulas shown below… =AVERAGEIF(range,criteria,[average_range])

AVERAGEIF example_1

Explanation of results

  1. Shirts sold in location A = (3 + 13 + 11 + 16 + 15 + 2 + 8 + 19 + 6) / 9 = 93 / 9 = 10.33
  2. White shirts sold = (16 + 15 + 2 + 9 + 17 + 19) / 6 = 78 / 6 = 13
  3. Medium shirts sold = (13 + 15 + 19 + 2 + 17 + 2) / 6 = 68 / 6 = 11.33

 

Go back to Excel Tutorials.