# AVERAGEIFS

## Rational

Find the average of a cell range based on multiple criteria.

## Description

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.

## Syntax

= AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

## Parameters

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

## Return Value

Average of numbers based on multiple criteria.

## Key Notes

• 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”.

Example

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.

### AVERAGE

The AVERAGE function returns an average of numbers positioned in one or multiple ranges, =AVERAGE(5,15,30) add numbers and divide by 3, and returns 16.67.

### AVERAGEIF

The AVERAGEIF function returns an average figure based on given criteria like =AVERAGEIF({6;8;10;3;5},”>5″) average numbers that are more than 5 and return 8.