Formula for adding values together in columns with certain criteria then averaging the data

[Slehvin]

Hopefully the title makes sense. I have searched and searched for the last couple months on various boards and search engines and only find basic CountIf type formulas.
I am trying to work out a formula that will take a number and add it to other numbers in the same row, though only if the column meets a certain criteria. Then average the numbers based on the occurrences of the criteria. Did that make sense?

Bob	11
Sarah	9
Bob	3
John	5
David	6
Sarah	2
David	3
Sarah	4

<tbody>
</tbody>

So I am trying to get a formula that will show Bob = 7, Sarah = 5, John = 5, David = 4.5
I am working in a spreadsheet with just about 300 entries with 10 to 20 new entries each month so by the end of the year I should be close to 400 entries.

 

[VoG]

Try in C1 copied down

=SUMIF(A$1:A$100,A1,B1:B1)/COUNTIF(A$1:A$100,A1)

 

[Dryver14]

Sheet1

	A	B	C	D	E	F
1	Bob	11			Bob	7
2	Sarah	9			Sarah	5
3	Bob	3			John	5
4	John	5			David	4.5
5	David	6
6	Sarah	2
7	David	3
8	Sarah	4

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula F1 =SUMIF($A$1:$A$100,E1,$B$1:$B$8)/COUNTIF($A$1:$A$100,E1) F2 =SUMIF($A$1:$A$100,E2,$B$1:$B$8)/COUNTIF($A$1:$A$100,E2) F3 =SUMIF($A$1:$A$100,E3,$B$1:$B$8)/COUNTIF($A$1:$A$100,E3) F4 =SUMIF($A$1:$A$100,E4,$B$1:$B$8)/COUNTIF($A$1:$A$100,E4) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[bosco_yip]

If you have Excel2007 or up, you can use :

( Using Dryver14's table)

F1, formula copy down

=AVERAGEIF(A$1:A$8,E1,B$1:B$8)

Regards

 

[Slehvin]

Thank you, I will try all these. I did forget to mention that I am using Excel 2010.