Learn how to use Excel Averageifs Formulas:
Excel Averageifs Formula averages a Set of values IF(S) a single or multiple conditions are met. In other words, averageifs function averages the number of cells that met one or more than one condition.
Averageifs Function Syntax: Averageifs(average_range, criteria_range1, criteria1, [criteria_range2, criteria2],..)
Average_range: Data in this range is averaged if all the criteria matches with their respective criteria_range.
Criteria_range1: The range of cells that get evaluated against its respective criteria.
Criteria1: It evaluates range and defines which cells to average from average_range.
[Criteria_range2, Criteria2]: Optional. Additional criteria_range and criteria. All criteria_range must have same number of rows and columns as the average_range.
Example: Excel Averageifs formula
In below example, calculate average sales for all the products where sales is done on or after 1-Apr-2013.
Above is the Sales Data with multiple entries of Sales for different products. In the above example, sales of different products on or after 1-Apr-2013 is derived with the help of Excel Averageifs Formulas.
Lets see how total sales of Pen sold on or after 1-Apr-2013 is calculated in cell D16
Average_range: Range D2:D11 is the sales data that is averaged if all criteria matches with its respective criteria_range.
Criteria-range1: Range C2:C11 is the product data.
Criteria1: An item (criteria) that is to be averaged. In this case, cell C16 is the criteria where product i.e., “Pen” is mentioned.
Criteria_range2: Range B2:B11 is the data where all the dates are mentioned.
Criteria2: Cell B16 is the second criteria where date on or after 1-Apr-2013 has been specified. Comparison Operators (>=) is used to specify the date that is “equal to or greater than 1-Apr-2013”.
Range and Cells has been fixed with “$” sign using F4 key to make the range and cell as absolute / mixed reference. Once Range reference has been done then formula in cell D16 is copied to Range D17:D19.
You may also consider learning Averageif Formula that work on single range and criteria only. But it works on office version prior to 2007 also.
Hope you may find this useful.