AVERAGEIF function.Average of cell values matching the search criteria.(Microsoft Excel)

03/27/2024

Japanese version.

The AVERAGEIF function is an extended version of the AVERAGE function that displays the average value of cells that satisfy the search criteria.
There is also a higher-level version of the AVERAGEIF function, the AVERAGEIFS function, which specifies multiple conditions.

How it works

=AVERAGEIF(range, criteria, Average_range)
NameOmissionDescription
rangeRequired argument.Specify a range of cells to be evaluated for the search criteria.
criteriaRequired argument.Specify the criteria to be averaged.
Average_rangeSame as range.Specify the range of cells to be averaged.
(Specify when the conditions and the range of the tally are different.)
Non-numeric cells within the average range are excluded.

Demonstrate

Can be used, for example, to average only products in a particular category.

It can also be used when using a large/small comparison as a basis. (e.g., an average over a certain amount of money).

Example of averaging data matching a specific string

The following is an example of obtaining the average of "Price" based on "Category".

In K4, specify the AVERAGEIF function as follows.

=AVERAGEIF($D$4:$D$11,J4,$E$4:$E$11)

The "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.

=AVERAGEIF($A$2:$A$10,"Pen*",$B$2:$B$10) 
=AVERAGEIF($A$2:$A$10,"*book*",$B$2:$B$10) 
=AVERAGEIF($A$2:$A$10,"*C",$B$2:$B$10)

Example of retrieval by setting a numerical threshold

The following example retrieves the average value of an Amount with a Price greater than 150.

=AVERAGEIF(E4:E11,">="&N4,G4:G11)
=AVERAGEIF(E4:E11,">=150",G4:G11)

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,AVERAGEIF(r,">=5")))
Row direction
=BYCOL(A1:C3,LAMBDA(c,AVERAGEIF(c,">=5")))
Column direction

---

Links

Microsoft Excel Functions Statistical