Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

AVERAGEIFS

Find the average of a cell range based on multiple criteria in Excel
Returns the average for the cells specified by multiple criteria

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.

Keep Reading Similar Functions:

Find the average of numbers in Excel

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.

Read More »
Find the average of numbers in Excel

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.

Read More »