Creating a 'league table' based on yes/no responses

[sobeitjedi]

Hi.

In my sheet I have data in a b c & d which can be a yes or no. Column E contains a name or email address.

I want to create a 'league table' where if there's a 'no' in a, it's 1 point, b =2 points, c=1 point, d=1 point

There are hundreds of rows and the names in E can be the same, I want the duplicate names removed, but the total score to include the duplicates.

Does that make sense?

Hope so!

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H
1	COL A	COL B	COL C	COL D	Name		Name	Score
2	no	yes	yes	no	a		a	8
3	yes	yes	yes	no	b		b	1
4	no	yes	no	yes	c		c	6
5	yes	yes	yes	no	a		d	6
6	no	yes	no	yes	d		e	3
7	yes	yes	no	no	e		x	3
8	yes	yes	yes	yes	a			0
9	no	yes	yes	yes	c			0
10	no	yes	yes	yes	a			0
11	yes	no	no	yes	c			0
12	yes	no	no	yes	x			0
13	no	no	yes	no	d			0
14	no	yes	yes	yes	e			0
15	no	no	yes	no	a			0
Sheet2

Cell Formulas
Range	Formula
H2	=SUMPRODUCT(($A$2:$D$15="no")*($E$2:$E$15=G2)*{1,2,1,1})
G2	{=IFERROR(INDEX($E$2:$E$15,MATCH(0,COUNTIF($E$2:$E$15,"<"&$E$2:$E$15)-SUM(COUNTIF($E$2:$E$15,"="&$G$1:$G1)),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Or were you looking for a VBA solution?