Find the average of a cell range based on multiple criteria.
The Excel AVERAGEIFS function returns an average number based on one or multiple given criteria. AVERAGEIFS can be used to sum values when adjacent cells meet criteria based on dates, numbers, and text.
= AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
average_range – The range of cells to evaluate for average against criteria
range1 – The first range to evaluate containing criteria1.
criteria1 – The criteria to apply for average in range1
range2 [optional] – The second range to evaluate containing criteria2
criteria2 [optional] – The criteria to apply for average in range2
Average of numbers based on multiple criteria.
- The AVERAGEIFS function is a plural variation of AVERAGEIF as it accepts multiple conditions or rule to average one or several ranges or array of numbers. The function can handle between 1 to 127 range or criterion pairs.
- AVERAGEIFS returns the #DIV/0! error value in case no value meets the criteria.
- Ignore Empty cell in range and average_range.
- For text criteria that include logical operators (>,<,<>,=) or wildcards symbols (*,?) for partial matches must be enclosed in double quotation marks (“) like “<30”. The user can find a literal question mark or asterisk by using a tilde (~) symbol before a question mark or asterisk (i.e., ~?, ~*). A criteria a*a will match with all cells containing a string beginning with “a” and ending in “a” like a text “abide” or “abate”.
To get average costs with Grade of each item. for example here to get average of Pastry with Grade = AVERAGEIFS($D5:$D23,$C5:$C23,G7,$B5:$B23,F7) it return Avg.cost=9.3 and Grade=1.