Each condition is provided with a separaterangeandcriteria.
However, the syntax used to apply conditions is a bit tricky because it is unusual in Excel.
See below for details.
The syntax for the AVERAGEIFS function depends on the criteria being evaluated.
Each separate condition will require arangeandcriteria.
The second argument,range1, is the range to which the first condition should be applied.
The third argument,criteria1, contains the condition that should be applied torange1, along with anylogical operators.
Additional conditions are applied by providing additionalrange/criteriaarguments.
Each condition requires a separaterangeandcriteria, and operators need to be enclosed in double quotes ("").
Note the difference in the two examples below.
Double quotes are also used for text values.
Notice the less thanoperator(which is text) is enclosed in quotes.
Wildcards
Thewildcardcharacters question mark (?
), asterisk(*), or tilde (~) can be used in criteria.
A question mark (?)
matches any one character and an asterisk (*) matches zero or more characters of any kind.
For example, to match a literal question mark (?
~?, ~*, ~~).
Note: the order of arguments is different between AVERAGEIFS andAVERAGEIF.
The range to average is always thefirstargument in AVERAGEIFS.
MODE Function
The Excel MODE function returns the most frequently occurring number in a numeric data set.
For example, =MODE(1,2,4,4,5,5,5,6) returns 5.
