Sumifs or Sum Product

[rex759]

Hello,
I’m stuck trying to figure out what to use to get the correct results

In short, I want to add the results for each employee’s numbers in Column B that belong to the correct Managers name in Column E. Then, show the averages starting in cell E14 (Managers Total)

In my example, the average for Mr. Smith would be 4.29 since it would include the results from Amy & Darren. Mr. Jones would be 4.5 because of Mike’s numbers and Mrs. Peabody would 5 for Bills number

I thought it wouldn’t be simple but how do you relate the information in Columns D:E with Columns A?

Hope this makes sense to someone. Thanks for any help.

Excel Workbook
	A	B	C	D	E
1	Results Name	Results Score		Employees	Manager
2	Amy	5		Alex	Mr Smith
3	Darren	5		Amy	Mr Smith
4	Darren	4		Mike	Mr Jones
5	Darren	3		Brennan	Mr Jones
6	Darren	4		Brian	Mrs Peabody
7	Darren	5		Frank	Mrs Peabody
8	Darren	4		Bill	Mrs Peabody
9	Bill	5		Phill	Mr Smith
10	Mike	4		Phill	Mr Smith
11	Mike	5		Darren	Mr Smith
12
13				Manager Totals
14				Mr Smith	4.29
15				Mr Jones	4.5
16				Mrs Peabody	5
Sheet1

Excel 2010

 

[Marcelo Branco]

Try

Array formula in E14 copied down
=SUM(SUMIF($A$2:$A$11,IF(E$2:E$11=D14,$D$2:$D$11),B$2:B$11))/SUM(COUNTIF(A$2:A$11,IF(E$2:E$11=D14,$D$2:$D$11)))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[rex759]

Brilliant!! I would have never got that! Works perfectly. Thanks for the quick response.

 

[Eric W]

A slightly shorter version, also needing Control+Shift+Enter:

=AVERAGE(IFERROR(($B$2:$B$11/COUNTIFS($D$2:$D$11,$A$2:$A$11,$E$2:$E$11,D14)),""))

It assumes that each employee is only entered once in the D:E table though, per manager.

 

[rex759]

Thank you Eric. I used a version of yours for another set of variables that I was stuck on that worked perfectly. Thanks very much