Returning averages using associated values among several datasets

MeredithQ

I'm not completely sure how to explain it...I'm building a sales dashboard, and I need to calculate several different averages associated to certain associates (volume, units, etc). The data I receive isn't formatted in the easiest way, so I'm looking for a formula that:

1. finds each time the associate's name is listed in column a (which can be multiple times if the associate has multiple loans) and then
2. averages the data in column b associated with the associate and returns the average in column c.

So if Lacy Lender has 3 loans, it will find her name 3 times among the the dataset of 100, use the loan volume of each of her 3 loans (found in column b) and then return her average loan volume.

I'll need to run that formula for each LO.

The data is also found in a different sheet, but I do know how to reference different sheets in the formula.

Hopefully this is easy - can anyone help? Thank you!!

KPH

You can use SUMIF to total all the loan amounts in col b if the name in col A matches the one in the row.

=SUMIF([Col A]:[Col A], [Col A]@row, [Col B]:[Col B])

You can use COUNTIF to find the number of times the name in the row appears in col A.

=COUNTIF([Col A]:[Col A], [Col A]@row)

If you divide the SUM of loan amounts by the COUNT of loans, you will have the average loan amount.

So, your formula (in Col C) would be

=SUMIF([Col A]:[Col A], [Col A]@row, [Col B]:[Col B]) / COUNTIF([Col A]:[Col A], [Col A]@row)