AVERAGEIFS function. Average of cell values matching multiple criteria.(Microsoft Excel)
Average of cell values matching multiple criteria.
The AVERAGEIF function has a single criteria and range, while this one has multiple criteria and ranges.
The MIN and MAX functions have only IFS versions with multiple specifications and the structure is similar.
Therefore, the AVERAGEIFS function is recommended over the AVERAGEIF function.
How it works
=AVERAGEIFS(criteria_range1, criteria1, criteria_range2, criteria2 ... criteria_range127, criteria127)
| Name | Omission | Description |
|---|---|---|
| Average_range | Required | Specify the range of cells to be averaged. Non-numeric cells are excluded. |
| criteria_range1 - 127 | Only 1 is required. | Specify the range of cells to be covered by the criteria. |
| criteria1 - 127 | Only 1 is required. | Specify the criteria to be included in the tally. |
Example Results
For example, it can be used to average sales in a specific category of products that are on sale.
It can also be used in cases where a large-to-small comparison is required.
(For example, averaging sales that are on sale and above a certain unit price)
Example of averaging data matching a specific string.
The following is an example of averaging the "Amount" of discounted products that match a certain category.
Specify the AVERAGEIFS function in cell K4 as follows
=AVERAGEIFS($G$4:$G$11,$D$4:$D$11,J4,$H$4:$H$11,TRUE)
The criteria_range is specified by absolute cell reference, because the reference point does not change even if the position is changed by copying.
Example of using wildcards
Forward, partial, and backward matching can also be achieved using wildcards.
Example of retrieval by setting a numerical threshold
The following example retrieves the average of "Amount" whose "Price" is more than 150 but less than 300.
=AVERAGEIFS(G4:G11,E4:E11,">="&N4,E4:E11,"<"&O4)
=AVERAGEIFS(G4:G11,E4:E11,">=150",E4:E11,"<300")
Comparison operators can be specified for search conditions, such as above or below a specific number.
Comparison operators must be enclosed in double-cotation marks and treated as character strings.
Spill
If you specify the criteria as a range of cells, it will be a Spill.
Also, Spill when using the BYROW or BYCOL function.
=BYROW(A1:C3,LAMBDA(r,AVERAGEIFS(r,r,">=2",r,"<=8")))
=BYCOL(A1:C3,LAMBDA(c,AVERAGEIFS(c,c,">=2",c,"<=8")))
---