The AVERAGEIFS function

The AVERAGEIFS function is a statistical function which is identical to the similarly named AVERAGEIF function, with the exception being that it handles multiple criteria. It is for that reason that it is valuable to build the habit of using the more versatile AVERAGEIFS function even when only a single criteria is needed. It will only handle numeric values, but allows for a more versatile selection process as you can place criteria on the average range itself should that be necessary.

   
This is the format of the AVERAGEIFS function:

=AVERAGEIFS(Average Range, Criteria Range 1, Criteria 1, Criteria Range 2, Criteria 2, etc…)

   
Here is an example of the AVERAGEIFS function:

=AVERAGEIFS(B1:B6,A1:A6,"A")

This function is valuable when compiling summary statistics based on consistent and clearly identified parameters, such as determining the average number of units which a client is purchasing of a given product.

An example of a functional implementation of this would be if you ran a small shop which keeps track of their customers by their email addresses, and you want to know how many units your most frequent customers purchase from you each time they place an order. Using the AVERAGEIFS function you can pull back the average units per transaction, by client, as long as your transaction data includes their email address or other identifiable information.

=AVERAGEIFS({4, 7, 2, 6, 3, 6, 10, 6, 28, 7, 48, 3, 7, 4},{“John”, “Mary”, “Alex”, “Bob”, “Bob”, “Mary”, “John”, “Bob”, “Alex”, “Mary”, “Alex”, “Mary”, “John”, “Bob”}, “Bob”)

=AVERAGE(6, 3, 6, 4) = 4.75