If average range is a blank or text, or if there are no cells that match all of the requirements, or if the cells in average range cannot be converted into numbers, you will receive #DIV0! Error. If a cell in a criterion range is empty, AVERAGEIFS considers it as a 0 value. Cells in the range that include TRUE will be regarded as 1, whereas cells that contain FALSE will be handled as 0. In contrast to the AVERAGEIF function, each criteria range in AVERAGEIFS must be the same size and shape as sum range. In criterion, the wildcard characters question mark (?) and asterisk (*) can be used. A question mark matches any specific character; an asterisk matches any string of characters. Type a tilde () before the letter to find a real question mark or asterisk.

AVERAGEIFS Function - TechnicalKingdom

EXCEL AVERAGEIFS FUNCTION

Excel AVERAGEIFS Function Introduction

The average of all cells that fulfil various conditions will be returned by this function.

Description of Excel AVERAGEIFS Function

Syntax:

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

Parameter list:

average range – the cell or cells to average, including integers or identifiers, arrays or references containing numbers.
Criteria range1, Criteria range2 – The first criteria range is required, while the succeeding criteria ranges are optional. You can specify up to 127 criteria for ranges.
Criteria 1, Criteria 2,…- Criteria 1 is required, while the following criteria are optional.

Step By Step Guide of AVERAGEIFS Function

In this example, we first calculate the average price of a hotel room from the A column if the town is in Paris, has more than 3 bedrooms, and has a Personal Chef.
The first parameter we need to give is the column from which we want the average, which is A2:A6.
The second and third parameters are the criteria range1 and criteria2. Criteria1 means the whole range with the town name and the town name to sort, which is B2:B6 and Paris.
The fourth and fifth parameters are the second criteria range (C2:C6) and the criteria (>2), which are the number of bedrooms and bedrooms greater than 2.

The sixth and seventh parameters are the third criteria range (D2:D6) and the criteria (Yes), which indicate whether or not Personal Chef is present.
Second, we calculate the average of the A column when the town is London, the bedroom is equal to or less than three, and there is no personal chef.
Using this function is similar to using the filter function in Excel.

Key Points

If average range is a blank or text, or if there are no cells that match all of the requirements, or if the cells in average range cannot be converted into numbers, you will receive #DIV0! Error.
If a cell in a criterion range is empty, AVERAGEIFS considers it as a 0 value.
Cells in the range that include TRUE will be regarded as 1, whereas cells that contain FALSE will be handled as 0.
In contrast to the AVERAGEIF function, each criteria range in AVERAGEIFS must be the same size and shape as sum range.
In criterion, the wildcard characters question mark (?) and asterisk (*) can be used. A question mark matches any specific character; an asterisk matches any string of characters. Type a tilde () before the letter to find a real question mark or asterisk.

EXCEL AVERAGEIFS FUNCTION

Excel AVERAGEIFS Function Introduction

Description of Excel AVERAGEIFS Function

Step By Step Guide of AVERAGEIFS Function

Key Points

Related Functions

HLOOKUP Function

HLOOKUP Function

VLOOKUP Function

VLOOKUP Function

INDEX Function

INDEX Function

MATCH Function

MATCH Function

DATE Function

DATE Function

IF Function

IF Function

OR Function

OR Function

AND Function

AND Function

RANK Function

RANK Function

SUMIF Function

SUMIF Function

XIRR Function

XIRR Function

WORKDAYS Function

WORKDAYS Function

Excel Functions

Excel Formulas

Excel Shortcuts