*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.

**QUERY**

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: **

=AVERAGEIF($B$3:$B$15,B$4,$D$3:$D$15)

**Function applicable:**

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)**

You can also debug formula using shortcut** F9** function key or under formulas, tab clicks on to **evaluate the formula.**

Let’s break the formula into **multiple small steps** to understand its application.

First formula

**IF($B$3:$B$15,B$4)**

## Download Your Example Excel File

** Step 1:**

Result: **3**

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.

**Step 2:**

=AVERAGEIF($B$3:$B$15,B$4,$D$3:$D$15)

Result: **41.67**

(50+40+35)= 125

(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 🙂