AVERAGEIF()

This article is also available in: Bahasa Malaysia Bahasa Indonesia

AVERAGEIF() is used to find the average of numbers that meet a certain criteria or condition.

AVERAGEIF(range, criteria, [average_range])

range – the range of cells that contains the condition you want to check

criteria – the condition you want to match. This can be a text, number or even another function. If you use text, this must be in double quotes, for example, “a text value”.

average_range – the range containing the values you want to average. This is optional. If you do not specify the average_range, Excel will use values found in range.

As an example, if we had a worksheet with daily sales, and wanted the average sales for one product, QuantumGlow LED Lamp.

In an empty cell, type in =AVERAGEIF(, then select the column with product names through to the column with amounts.

Your formula will now look like this:

=AVERAGEIF(C2:F20,

This is the range part of the formula.

Next, specify the criteria, which is QuantumGlow LED Lamp.

=AVERAGEIF(C2:F20,"QuantumGlow LED Lamp",

Finally, select the column with the sales amounts to complete the formula and press enter.

=AVERAGEIF(C2:F20,"QuantumGlow LED Lamp", F2:F20)

This is the average_range.

You can also use wild cards in the criteria as follows:

=AVERAGEIF(C2:F20, "QuantumGlow*", F2:F20)

This article is also available in: Bahasa Malaysia Bahasa Indonesia