Formulas and Functions

Chapter 10 Statistical Functions 235
Usage Notes
For each of the  test-values and condition pairs, the corresponding (same position
within range or array) value is compared to the conditional test. If all of the conditional
tests are met, the corresponding value in avg-values is included in the average.
 avg-values and all test-values collections must be the same size.
Examples
Given the following table:
=AVERAGEIFS(D2:D13,A2:A13,”<40”,B2:B13,”=M”) returns 56000, the average income of males
(indicated by an “M” in column B) under the age of forty.
=AVERAGEIFS(D2:D13,A2:A13,”<40”,B2:B13,”=M”,C2:C13,”=S”) returns 57000, the average income of
males who are single (indicated by an “S” in column C) under the age of forty.
=AVERAGEIFS(D2:D13,A2:A13,”<40”,B2:B13,”=M”,C2:C13,”=M”) returns 55000, the average income of
males who are married (indicated by an “M” in column C) under the age of forty.
=AVERAGEIFS(D2:D13,A2:A13,”<40”,B2:B13,”=F”) returns approximately 59333, the average income of
females (indicated by an “F” in column B) who are under the age of forty.
Related Topics
For related functions and additional information, see:
“AVERAGE” on page 231
“AVERAGEA” on page 232
“AVERAGEIF” on page 233
“Specifying Conditions and Using Wildcards” on page 360
“Listing of Statistical Functions” on page 225
“Value Types” on page 36
“The Elements of Formulas” on page 15