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)Did you find this article helpful? Please rate and also share your thoughts in the comments section below.