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

04/23/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)`

## 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")))`
`=BYCOL(A1:C3,LAMBDA(c,AVERAGEIF(c,">=5")))`

---