Returning averages using associated values among several datasets
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:
 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
 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!!
Answers

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)
Help Article Resources
Categories
Check out the Formula Handbook template!