New Member!! Help required with returning name of highest scorer based on criteria

[Snake68]

		Skills
Seniority	Name	a	b	c	d	e	Total
RM	jon	6	9	4	5	5	29​
M	james	1	2	1	0	2	6​
RM	jack	2	3	5	8	3	21​
D	joe	1	6	7	3	8	25​
RM	jeff	8	9	2	3	5	27​

Hello!

I require help in returning the name of the person with the highest score based on certain criteria. E.g - I require somebody of 'RM' seniority who has the highest total for skills 'a,c & e'. This would be Jeff, who'd score 15 points.

I've tried all sorts of formuals (index match, sumproduct etc, but am not getting anywhere! Please help!

Many thanks,
S

 

[kweaver]

Welcome to Mr Excel.
Isn't Jon in RM = 15 as well?

 

[Eric W]

I also see Jon as a match. Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Seniority	Name	a	b	c	d	e	Total		Seniority	Max of A/C/E	Name
2	RM	jon	6	9	4	5	5	29		RM	15	jon
3	M	james	1	2	1	0	2	6				jon
4	RM	jack	2	3	5	8	3	21
5	D	joe	1	6	7	3	8	25
6	RM	jeff	8	9	2	3	5	27
Sheet5

Cell Formulas
Range	Formula
K2	K2	=MAX(MMULT(C2:G6,{1;0;1;0;1})*(A2:A6=J2))
L2	L2	=INDEX(B2:B6,MATCH(K2,MMULT(C2:G6,{1;0;1;0;1})*(A2:A6=J2),0))
L3	L3	=INDEX(B2:B6,MATCH(MAX(MMULT(C2:G6,{1;0;1;0;1})*(A2:A6="RM")),MMULT(C2:G6,{1;0;1;0;1})*(A2:A6="RM"),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

The L2 formula breaks it into parts. the L3 formula combines it into a single formula.

 

[kweaver]

I think this array formula does it also but doesn't handle ties.

Book7
	A	B	C	D	E	F	G	H
1			Skills
2	Seniority	Name	a	b	c	d	e	Total
3	RM	jon	6	9	4	5	4	28
4	M	james	1	2	1	0	2	6
5	RM	jack	2	3	5	8	3	21
6	D	joe	1	6	7	3	8	25
7	RM	jeff	8	9	2	3	5	27
8
9	Seniority	RM
10	Highest	jeff
Sheet1

Cell Formulas
Range	Formula
H3:H7	H3	=SUM(C3:G3)
B10	B10	=INDEX(B3:B7,SUM(ROW(1:5)*(((B9=A3:A7)*(C3:C7+E3:E7+G3:G7))=MAX((B9=A3:A7)*(C3:C7+E3:E7+G3:G7)))),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Snake68]

Thanks guys, both solutions give me what I require in this instance. However, what would need to be done to the formula for me to choose any combo of variables? E.g. abc, cde bde, etc? Is there a way for the formula to reference these and then do the calculation?

Cheers!
S

 

[Eric W]

In my formula, you can see this array constant:

{1;0;1;0;1}

That tells you which columns you want to add. In this case, the 1st, 3rd, and 5th. If you want 1, 2 and 4, change it to:

{1;1;0;1;0}

With a little modification to the formula, you can put "11010" as a text value in another cell and thus dynamically change what columns to add. Like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Seniority	Name	a	b	c	d	e	Total		Seniority	Max of A/C/E	Name
2	RM	jon	6	9	4	5	5	29		RM	16	joe
3	M	james	1	2	1	0	2	6				jack	00111
4	RM	jack	2	3	5	8	3	21
5	D	joe	1	6	7	3	8	25
6	RM	jeff	8	9	2	3	5	27
Sheet1

Cell Formulas
Range	Formula
K2	K2	=MAX(MMULT(C2:G6,{1;0;1;0;1})*(A2:A6=J2))
L2	L2	=INDEX(B2:B6,MATCH(K2,MMULT(C2:G6,{1;0;1;0;1})*(A2:A6=J2),0))
L3	L3	=INDEX(B2:B6,MATCH(MAX(MMULT(C2:G6,MID(M3,{1;2;3;4;5},1)+0)*(A2:A6="RM")),MMULT(C2:G6,MID(M3,{1;2;3;4;5},1)+0)*(A2:A6="RM"),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Snake68]

Thanks this is very helpful!