Learn how to use Excel Averageif Formula:
Excel Averageif Formula AVERAGE the data value IF it meets specified criteria or condition. Lets understand this with the help of Averageif Formula syntax and an example.
Averageif Function Syntax: Averageif(range, criteria, [average_range])
Range: The range of Cells that gets evaluated against the criteria.
Criteria: It evaluates Range and defines which cells to average from average_range.
[Average_Range]: Optional. It contains the set of values to be averaged. Average is done if Criteria matches with the Range. If average_range is omitted then range is treated as average_range.
Example: Excel Averageif Formula
In the below data, we need to find the average sales of all four products.
Above is the Sales Data with multiple entries of sales for different products. Excel Averageif Formula has been used to find out the average values of Sales of all 4 products i.e., Pen, Pencil, Eraser & Color.
Lets see how Average Sales of Pen is calculated in Cell D15:
Range: Range C2:C11 is the data where Products are mentioned.
Criteria: Here we need to specify a product that we wants to average. In other words, Cell C15 is the criteria where Product i.e., “Pen” has been mentioned.
Average_Range: It needs to be the sales data that has been mentioned in Range D2:D11.
Range and Cells has been fixed with “$” sign using F4 Key to make the Range & Cell as Absolute / Mixed Reference. Once Range Reference has been done then Formula in Cell D15 is copied to Range D16:D18.
Averageif works only with single Continuous Range & Criteria. However, Averageifs works with multiple Continuous Range and Criteria. Averageifs works only with Excel 2007 and later versions while Averageif works with prior versions also.
You may also consider learning Averageifs Formula.
Hope you may find this useful.