After reading this article you will be able to find average based on the criteria of another column i.e. specific name that is occurring multiple times.
In case you want to calculate the Average based on criteria matched name in a column and multiple rows we can apply this formula.
We have columns Customer, Item and Price. Our task is to get the average of the price in multiple rows with specific customers in column (B).
Formula to implement:
The Excel AVERAGEIF Function calculates the average (arithmetic mean) of the numbers in a range that meet the applied criteria. The criteria for AVERAGEIF supports logical operators (>, <, <>, =) and wildcards (*,?) for partial matching.
The AVERAGEIF function has the following arguments, the first 2 are required, the last one is optional:
Range: A lookup range to apply criteria.
Criteria: The criteria that must satisfy in the given Range.
Average_ range [Optional]: The range of cells you actually want to average if it satisfies the condition.
Formula Explanation (Step by Step)
Let’s break the formula into multiple small steps to understand its application.
Download Your Example Excel File
It checks in the range (B3 to B15) with criteria name – “preya” and then return how many times given text appear in column – 3 times in our case.
(125/3)= 41.67 ‘Sum next cell value divided by number of occurrence
Now it sums all prices given in the next column matching name i.e. Preya in the given range (D3 to D15) then divide it by counts.
Go ahead and simply change the customer name or price to see how formula works 🙂