Quiz Scores Leaderboard

[stefanaalten]

Hi,

I'm trying to compile a "leaderboard" for our weekly quizzes, which since the start of the pandemic have become our mainstay social activity (and good fun they are too). It's only among friends but can get pretty competitive! I have produced a spreadsheet to record the weekly scores, but want to produce an overall ranking, over time, showing which team has come in which place in a given week (based on the total score for that week).

So the "output" I'm looking to produce shows for a given quiz date basis which team has come 1st, 2nd, 3rd, 4th (i.e. the actual scores are immaterial - it's only the ranking that matters).

Can anyone please help me on my way? I've tried playing around with pivot tables but am getting mightily confused! Probably a really simple "problem" to solve but I'm not really sure how to go about, and any help would be appreciated!

My source data is like this:

Scores on the Doors - Totals.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Row ID	Quiz Date	Host	Round No.	Round Title	A&R	E&M	M&M	N&S	W&M
2	1	2021-01-08	W&M	1	Colours	3	5	4	3
3	2	2021-01-08	W&M	2	Water	6	3	10	8
4	3	2021-01-08	W&M	3	Sheep or Rabbit	6	5	7	5
5	4	2021-01-08	W&M	4	70's	8	9.5	10.5	9.5
6	5	2021-01-08	W&M	5	Drink	3	2	2	6
7	6	2021-01-22	E&M	1	80's pop	5		11	6	9
8	7	2021-01-22	E&M	2	Films/songs	10		12	4	16
9	8	2021-01-22	E&M	3	G7	13		14	13	15
10	9	2021-01-22	E&M	4	Film soundtracks	4		4	7	8
11	10	2021-01-22	E&M	5	Live Aid	8		10	7	13
12	11	2021-01-29	A&R	1	Fruit & Veg		12	15	15	15
13	12	2021-01-29	A&R	2	Trump		6	3	5	4
14	13	2021-01-29	A&R	3	Dance Moves		7	4	8	7
15	14	2021-01-29	A&R	4	In Continent		6	5	9	11
16	15	2021-01-29	A&R	5	Spot the Dog		25	25	19	26
17	16	2021-02-05	M&M	1	Knobs & Knockers	2.5	5		3.5	5
18	17	2021-02-05	M&M	2	DIY	2.5	4		3.5	5
19	18	2021-02-05	M&M	3	Arts & Crafts	2	5		7	4.5
20	19	2021-02-05	M&M	4	Sport	4	4		2	5
21	20	2021-02-05	M&M	5	Picture Round	14	18.5		17	21.5
22	21	2021-02-05	M&M	6	Silly	6	5.5		3	6
23	22	2021-02-12	W&M	1	1980's	4	5	5	5
24	23	2021-02-12	W&M	2	British Wildlife	6	3	5	6
25	24	2021-02-12	W&M	3	American or Canadian	7	8	7	7
26	25	2021-02-12	W&M	4	Just Desserts	7.5	5.5	8	8
27	26	2021-02-12	W&M	5	Cats	19	21	27	15.5
28	27	2021-02-19	N&S	1	Where Am I?	6	7	7		5
29	28	2021-02-19	N&S	2	Alphabetical Capitals	9	12	15		12
30	29	2021-02-19	N&S	3	Hungarian Phrasebook	5	5	8		7
31	30	2021-02-19	N&S	4	Parlez-vous Inuit?	7	10	10		10
32	31	2021-02-19	N&S	5	World Music	6	7	6		9
33	32	2021-02-19	N&S	6	Bonus Round: Fruit & Veg	14.5	11	14		13
34	33	2021-02-26	E&M	1	By the Bard	7		7	5	6
35	34	2021-02-26	E&M	2	Tallest and Heaviest	6		7	3	3
36	35	2021-02-26	E&M	3	Good Citizen	6		5	5	8
37	36	2021-02-26	E&M	4	Sex Pests	10		13	14	13
38	37	2021-02-26	E&M	5	Colours	4		3	2	4
Scores

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Row ID	Quiz Date	Host	Round No.	Round Title	A&R	E&M	M&M	N&S	W&M			22/01/2021
2	1	08/01/2021	W&M	1	Colours	3	5	4	3				A&R	40	3
3	2	08/01/2021	W&M	2	Water	6	3	10	8				E&M	0	5
4	3	08/01/2021	W&M	3	Sheep or Rabbit	6	5	7	5				M&M	51	2
5	4	08/01/2021	W&M	4	70's	8	9.5	10.5	9.5				N&S	37	4
6	5	08/01/2021	W&M	5	Drink	3	2	2	6				W&M	61	1
7	6	22/01/2021	E&M	1	80's pop	5		11	6	9
8	7	22/01/2021	E&M	2	Films/songs	10		12	4	16
9	8	22/01/2021	E&M	3	G7	13		14	13	15
10	9	22/01/2021	E&M	4	Film soundtracks	4		4	7	8
11	10	22/01/2021	E&M	5	Live Aid	8		10	7	13
12	11	29/01/2021	A&R	1	Fruit & Veg		12	15	15	15
13	12	29/01/2021	A&R	2	Trump		6	3	5	4
14	13	29/01/2021	A&R	3	Dance Moves		7	4	8	7
15	14	29/01/2021	A&R	4	In Continent		6	5	9	11
16	15	29/01/2021	A&R	5	Spot the Dog		25	25	19	26
17	16	05/02/2021	M&M	1	Knobs & Knockers	2.5	5		3.5	5
18	17	05/02/2021	M&M	2	DIY	2.5	4		3.5	5
19	18	05/02/2021	M&M	3	Arts & Crafts	2	5		7	4.5
20	19	05/02/2021	M&M	4	Sport	4	4		2	5
21	20	05/02/2021	M&M	5	Picture Round	14	18.5		17	21.5
22	21	05/02/2021	M&M	6	Silly	6	5.5		3	6
23	22	12/02/2021	W&M	1	1980's	4	5	5	5
24	23	12/02/2021	W&M	2	British Wildlife	6	3	5	6
25	24	12/02/2021	W&M	3	American or Canadian	7	8	7	7
26	25	12/02/2021	W&M	4	Just Desserts	7.5	5.5	8	8
27	26	12/02/2021	W&M	5	Cats	19	21	27	15.5
28	27	19/02/2021	N&S	1	Where Am I?	6	7	7		5
29	28	19/02/2021	N&S	2	Alphabetical Capitals	9	12	15		12
30	29	19/02/2021	N&S	3	Hungarian Phrasebook	5	5	8		7
31	30	19/02/2021	N&S	4	Parlez-vous Inuit?	7	10	10		10
32	31	19/02/2021	N&S	5	World Music	6	7	6		9
33	32	19/02/2021	N&S	6	Bonus Round: Fruit & Veg	14.5	11	14		13
34	33	26/02/2021	E&M	1	By the Bard	7		7	5	6
35	34	26/02/2021	E&M	2	Tallest and Heaviest	6		7	3	3
36	35	26/02/2021	E&M	3	Good Citizen	6		5	5	8
37	36	26/02/2021	E&M	4	Sex Pests	10		13	14	13
38	37	26/02/2021	E&M	5	Colours	4		3	2	4
Lists

Cell Formulas
Range	Formula
N2:N6	N2	=SUMPRODUCT(($B$2:$B$38=$M$1)*($F$1:$J$1=M2)*($F$2:$J$38))
O2:O6	O2	=RANK(N2,$N$2:$N$6)

 

[Snakehips]

For what it's worth, I'll throw this in. It requires some helper columns in T:Z that can be hidden.

MRXLMAY21.xlsm
	M	N	O	P	Q	R	S	T	U	V	W	X
1	Quiz\Team	A&R	E&M	M&M	N&S	W&M		A&R	E&M	M&M	N&S	W&M
2	Overall	5th	4th	1st	2nd	3rd		66	80.5	136.5	124.5	124
3	08/01/2021	3rd	4th	1st	2nd	Hosts		26	24.5	33.5	31.5	0
4	22/01/2021	3rd	Hosts	2nd	4th	1st		40	0	51	37	61
5	29/01/2021	Hosts	2nd	4th	2nd	1st		0	56	52	56	63
6								0	0	0	0	0
Sheet7

Cell Formulas
Range	Formula
N2:R2	N2	=IF(M3="","",CHOOSE(RANK(T2,$T2:$X2),"1st","2nd","3rd","4th","5th"))
N3:R6	N3	=IF(M3="","",CHOOSE(RANK(T3,$T3:$X3),"1st","2nd","3rd","4th","Hosts"))
T2:X2	T2	=SUM(T1:T300)
T3:X6	T3	=SUMIF($B:$B,$M3,F:F)

 

[Fluff]

Another option (in M9) if you have 365

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Row ID	Quiz Date	Host	Round No.	Round Title	A&R	E&M	M&M	N&S	W&M			19/02/2021
2	1	08/01/2021	W&M	1	Colours	3	5	4	3				A&R	47.5	4
3	2	08/01/2021	W&M	2	Water	6	3	10	8				E&M	52	3
4	3	08/01/2021	W&M	3	Sheep or Rabbit	6	5	7	5				M&M	60	1
5	4	08/01/2021	W&M	4	70's	8	9.5	10.5	9.5				N&S	0	5
6	5	08/01/2021	W&M	5	Drink	3	2	2	6				W&M	56	2
7	6	22/01/2021	E&M	1	80's pop	5		11	6	9
8	7	22/01/2021	E&M	2	Films/songs	10		12	4	16			19/02/2021
9	8	22/01/2021	E&M	3	G7	13		14	13	15			M&M	60	1
10	9	22/01/2021	E&M	4	Film soundtracks	4		4	7	8			W&M	56	2
11	10	22/01/2021	E&M	5	Live Aid	8		10	7	13			E&M	52	3
12	11	29/01/2021	A&R	1	Fruit & Veg		12	15	15	15			A&R	47.5	4
13	12	29/01/2021	A&R	2	Trump		6	3	5	4			N&S	0	Host
14	13	29/01/2021	A&R	3	Dance Moves		7	4	8	7
15	14	29/01/2021	A&R	4	In Continent		6	5	9	11
16	15	29/01/2021	A&R	5	Spot the Dog		25	25	19	26
17	16	05/02/2021	M&M	1	Knobs & Knockers	2.5	5		3.5	5
18	17	05/02/2021	M&M	2	DIY	2.5	4		3.5	5
19	18	05/02/2021	M&M	3	Arts & Crafts	2	5		7	4.5
20	19	05/02/2021	M&M	4	Sport	4	4		2	5
21	20	05/02/2021	M&M	5	Picture Round	14	18.5		17	21.5
22	21	05/02/2021	M&M	6	Silly	6	5.5		3	6
23	22	12/02/2021	W&M	1	1980's	4	5	5	5
24	23	12/02/2021	W&M	2	British Wildlife	6	3	5	6
25	24	12/02/2021	W&M	3	American or Canadian	7	8	7	7
26	25	12/02/2021	W&M	4	Just Desserts	7.5	5.5	8	8
27	26	12/02/2021	W&M	5	Cats	19	21	27	15.5
28	27	19/02/2021	N&S	1	Where Am I?	6	7	7		5
29	28	19/02/2021	N&S	2	Alphabetical Capitals	9	12	15		12
30	29	19/02/2021	N&S	3	Hungarian Phrasebook	5	5	8		7
31	30	19/02/2021	N&S	4	Parlez-vous Inuit?	7	10	10		10
32	31	19/02/2021	N&S	5	World Music	6	7	6		9
33	32	19/02/2021	N&S	6	Bonus Round: Fruit & Veg	14.5	11	14		13
34	33	26/02/2021	E&M	1	By the Bard	7		7	5	6
35	34	26/02/2021	E&M	2	Tallest and Heaviest	6		7	3	3
36	35	26/02/2021	E&M	3	Good Citizen	6		5	5	8
37	36	26/02/2021	E&M	4	Sex Pests	10		13	14	13
38	37	26/02/2021	E&M	5	Colours	4		3	2	4
39
Lists

Cell Formulas
Range	Formula
N2:N6	N2	=SUMPRODUCT(($B$2:$B$38=$M$1)*($F$1:$J$1=M2)*($F$2:$J$38))
O2:O6	O2	=RANK(N2,$N$2:$N$6)
M9:O13	M9	=LET(Totals,SUBTOTAL(9,OFFSET(F2:J38,MATCH(M8,B2:B38,0)-1,SEQUENCE(5,,0),COUNTIF(B2:B38,M8),1)),Rnk,MATCH(Totals,SORT(Totals,,-1),0),CHOOSE({1,2,3},SORTBY(TRANSPOSE(F1:J1),Totals,-1),SORTBY(Totals,Rnk),SUBSTITUTE(SORT(Rnk),5,"Host")))
Dynamic array formulas.

 

[stefanaalten]

Snakehips, Fluff, thank you very much for both your suggested solutions! I have gone for the one suggested by Snakehips, but with a small change. I have changed the ordinal numbers (1st, 2nd, 3rd, 4th) to just plain numbers (1, 2, 3, 4) and instead of basing the overall ranking (in N2:R2) on the total number of points, I have changed this to:

=COUNTIF(N3:N18,1)*4+COUNTIF(N3:N18,2)*3+COUNTIF(N3:N18,3)*2+COUNTIF(N3:N18,4)

i.e. 4 points for a win, 3 points for 2nd place, 2 points for 3rd place, and 1 point for 4th place.

Can I just ask whether that seems like a "fair" system to get a good idea of who the overall winners are?

Thanks again!

 

[stefanaalten]

Ah ... I just spotted a flaw in the overall logic in Snakehips suggested solution (or rather, I didn't make the situation clear enough!) Not all teams are present at every quiz, For instance, M&M did not take part on 2021-04-09 and 2021-04-23 but the calculations (in T3:X19) turn this into effectively coming last in the quiz that week and also results in no team being flagged as "hosts" (in N3:R19).

Scores on the Doors - Totals.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Row ID	Quiz Date	Host	Round No.	Round Title	A&R	E&M	M&M	N&S	W&M			Quiz/Team	A&R	E&M	M&M	N&S	W&M		A&R	E&M	M&M	N&S	W&M
2	1	2021-01-08	W&M	1	Colours	3	5	4	3				Overall	26	34	38	27	39
3	2	2021-01-08	W&M	2	Water	6	3	10	8				2021-01-08	3	4	1	2	Hosts		26	24.5	33.5	31.5	0
4	3	2021-01-08	W&M	3	Sheep or rabbit	6	5	7	5				2021-01-15	3	4	1	Hosts	2		38.5	34	47	0	45
5	4	2021-01-08	W&M	4	70's	8	9.5	10.5	9.5				2021-01-22	3	Hosts	2	4	1		40	0	51	37	61
6	5	2021-01-08	W&M	5	Drink	3	2	2	6				2021-01-29	Hosts	2	4	2	1		0	56	52	56	63
7	6	2021-01-15	N&S	1	Classic Citroëns	5	4	6		7			2021-02-05	4	2	Hosts	3	1		31	42	0	36	47
8	7	2021-01-15	N&S	2	Numbers round	4	5	4		1			2021-02-12	2	3	1	4	Hosts		43.5	42.5	52	41.5	0
9	8	2021-01-15	N&S	3	Watching the detectives	8.5	8	16		15			2021-02-19	4	3	1	Hosts	2		47.5	52	60	0	56
10	9	2021-01-15	N&S	4	Name that tune	12	8	15		13			2021-02-26	3	Hosts	1	4	2		33	0	35	29	34
11	10	2021-01-15	N&S	5	Board with lockdown	9	9	6		9			2021-03-05	Hosts	4	1	3	2		0	50	56	51	52
12	11	2021-01-22	E&M	1	80's pop	5		11	6	9			2021-03-12	4	1	Hosts	2	2		54	65	0	55	55
13	12	2021-01-22	E&M	2	Films/songs	10		12	4	16			2021-03-19	3	2	1	4	Hosts		35	45	55	29	0
14	13	2021-01-22	E&M	3	G7	13		14	13	15			2021-03-26	2	1	4	Hosts	3		36	38.5	30.5	0	35
15	14	2021-01-22	E&M	4	Film soundtracks	4		4	7	8			2021-04-09	1	4	4	3	2		62.5	0	0	54	61.5
16	15	2021-01-22	E&M	5	Live Aid	8		10	7	13			2021-04-23	4	1	4	3	2		0	53.75	0	50	53.5
17	16	2021-01-29	A&R	1	Fruit & veg		12	15	15	15			2021-04-30	4	3	2	1	Hosts		41.5	52.5	54	59.5	0
18	17	2021-01-29	A&R	2	Trump		6	3	5	4			2021-05-07	4	2	Hosts	3	1		27	33	0	31	42
19	18	2021-01-29	A&R	3	Dance moves		7	4	8	7			2021-05-14	3	2	3	Hosts	1		46	50	46	0	65
20	19	2021-01-29	A&R	4	In continent		6	5	9	11
21	20	2021-01-29	A&R	5	Spot the dog		25	25	19	26
22	21	2021-02-05	M&M	1	Knobs & knockers	2.5	5		3.5	5
23	22	2021-02-05	M&M	2	DIY	2.5	4		3.5	5
24	23	2021-02-05	M&M	3	Arts & crafts	2	5		7	4.5
25	24	2021-02-05	M&M	4	Sport	4	4		2	5
26	25	2021-02-05	M&M	5	Picture round	14	18.5		17	21.5
27	26	2021-02-05	M&M	6	Silly	6	5.5		3	6
28	27	2021-02-12	W&M	1	1980's	4	5	5	5
29	28	2021-02-12	W&M	2	British wildlife	6	3	5	6
30	29	2021-02-12	W&M	3	American or Canadian	7	8	7	7
31	30	2021-02-12	W&M	4	Just desserts	7.5	5.5	8	8
32	31	2021-02-12	W&M	5	Cats	19	21	27	15.5
33	32	2021-02-19	N&S	1	Where am I?	6	7	7		5
34	33	2021-02-19	N&S	2	Alphabetical capitals	9	12	15		12
35	34	2021-02-19	N&S	3	Hungarian phrasebook	5	5	8		7
36	35	2021-02-19	N&S	4	Parlez-vous Inuit?	7	10	10		10
37	36	2021-02-19	N&S	5	World music	6	7	6		9
38	37	2021-02-19	N&S	6	Bonus round: fruit & veg	14.5	11	14		13
39	38	2021-02-26	E&M	1	By the bard	7		7	5	6
40	39	2021-02-26	E&M	2	Tallest and heaviest	6		7	3	3
41	40	2021-02-26	E&M	3	Good citizen	6		5	5	8
42	41	2021-02-26	E&M	4	Sex pests	10		13	14	13
43	42	2021-02-26	E&M	5	Colours	4		3	2	4
44	43	2021-03-05	A&R	1	Science & nature		7	10	4	8
45	44	2021-03-05	A&R	2	Ali plays piano		12	11	16	15
46	45	2021-03-05	A&R	3	80s prices		8	9	5	2
47	46	2021-03-05	A&R	4	Summertime		5	7	6	8
48	47	2021-03-05	A&R	5	Paintings		18	19	20	19
49	48	2021-03-12	M&M	1	Music	3	5		2	2
50	49	2021-03-12	M&M	2	On the scent	6	8		7	9
51	50	2021-03-12	M&M	3	Natty headgear	5	6.5		5	6.5
52	51	2021-03-12	M&M	4	Use your loaf	4	2		4	3
53	52	2021-03-12	M&M	5	Bloody nutter	6	6		5	4
54	53	2021-03-12	M&M	6	Doctors	30	37.5		32	30.5
55	54	2021-03-19	W&M	1	Going green	5	7	8	7
56	55	2021-03-19	W&M	2	The long, tall and short of it	2	4.5	5	1
57	56	2021-03-19	W&M	3	Beside the seaside	2	3.5	7	4
58	57	2021-03-19	W&M	4	Dead or alive	12	13	16	11
59	58	2021-03-19	W&M	5	80s pop	14	17	19	6
60	59	2021-03-26	N&S	1	Lookback on lockdown	2.5	3.5	4		3.5
61	60	2021-03-26	N&S	2	Boat bits	5	8	7		4
62	61	2021-03-26	N&S	3	De imperatoribus romanis	3	3	2		3
63	62	2021-03-26	N&S	4	Etymology	4.5	5	2.5		6.5
64	63	2021-03-26	N&S	5	Smullyanisms	7	6	4		8
65	64	2021-03-26	N&S	6	Hot homonyms	14	13	11		10
66	65	2021-04-09	E&M	1	General knowledge	14			14	14
67	66	2021-04-09	E&M	2	Mark plays guitar	17			11	17
68	67	2021-04-09	E&M	3	BAR	7.5			9	7
69	68	2021-04-09	E&M	4	Not my first rodeo	9			3	7.5
70	69	2021-04-09	E&M	5	Flowers	15			17	16
71	70	2021-04-23	A&R	1	Catchphrases		9		8.5	13
72	71	2021-04-23	A&R	2	No name		10		11	11
73	72	2021-04-23	A&R	3	Numbers		7		8	6
74	73	2021-04-23	A&R	4	Round 4		7		7	7
75	74	2021-04-23	A&R	5	What comes next		20.75		15.5	16.5
76	75	2021-04-30	W&M	1	Oscars	3	6	6	5
77	76	2021-04-30	W&M	2	Butterfly or moth	4	6	6	3
78	77	2021-04-30	W&M	3	Single letter answers	10	13	19	17
79	78	2021-04-30	W&M	4	Food of the world	24.5	27.5	23	34.5
80	79	2021-05-07	M&M	1	Watching the detectives	3	5		5	5
81	80	2021-05-07	M&M	2	Lettuce or tomato	6	8		5	7
82	81	2021-05-07	M&M	3	Name the year	2	3		6	7
83	82	2021-05-07	M&M	4	Smells	7	5		6	7
84	83	2021-05-07	M&M	5	Picture round	9	12		9	16
85	84	2021-05-14	N&S	1	Vive la revolution!	15	14	12		16
86	85	2021-05-14	N&S	2	Film soundtracks	6	3	3		6
87	86	2021-05-14	N&S	3	Connections - hops	6	7	7		14
88	87	2021-05-14	N&S	4	Impressionist art	8	10	10		8
89	88	2021-05-14	N&S	5	Saintly cities	8	12	12		17
90	89	2021-05-14	N&S	6	Connections 2 - Tintin	3	4	2		4
91	90
92	91
93	92
94	93
95	94
96	95
97	96
Scores

Cell Formulas
Range	Formula
N2:R2	N2	=COUNTIF(N3:N18,1)*4+COUNTIF(N3:N18,2)*3+COUNTIF(N3:N18,3)*2+COUNTIF(N3:N18,4)
N3:R19	N3	=IF(M3="","",CHOOSE(RANK(T3,$T3:$X3),1,2,3,4,"Hosts"))
T3:X19	T3	=SUMIF($B:$B,$M3,F:F)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

Rather than giving 4, 3, 2 & 1 points I would tend to give a bit more weight to them & would also use an average so they are not penalised if they don't play.

+Fluff 1.xlsm
	M	N	O	P	Q	R	S	T	U	V	W	X
1	Quiz/Team	A&R	E&M	M&M	N&S	W&M		A&R	E&M	M&M	N&S	W&M
2	Overall	26	46	70	30	67
3	08/01/2021	3	4	1	2	Hosts		26	24.5	33.5	31.5	0
4	15/01/2021	3	4	1	Hosts	2		38.5	34	47	0	45
5	22/01/2021	3	Hosts	2	4	1		40	0	51	37	61
6	29/01/2021	Hosts	2	4	2	1		0	56	52	56	63
7	05/02/2021	4	2	Hosts	3	1		31	42	0	36	47
8	12/02/2021	2	3	1	4	Hosts		43.5	42.5	52	41.5	0
9	19/02/2021	4	3	1	Hosts	2		47.5	52	60	0	56
10	26/02/2021	3	Hosts	1	4	2		33	0	35	29	34
11	05/03/2021	Hosts	4	1	3	2		0	50	56	51	52
12	12/03/2021	4	1	Hosts	2	2		54	65	0	55	55
13	19/03/2021	3	2	1	4	Hosts		35	45	55	29	0
14	26/03/2021	2	1	4	Hosts	3		36	38.5	30.5	0	35
15	09/04/2021	1	Hosts	-	3	2		62.5	0	0	54	61.5
16	23/04/2021	Hosts	1	-	3	2		0	53.75	0	50	53.5
17	30/04/2021	4	3	2	1	Hosts		41.5	52.5	54	59.5	0
18	07/05/2021	4	2	Hosts	3	1		27	33	0	31	42
19	14/05/2021	3	2	3	Hosts	1		46	50	46	0	65
Lists

Cell Formulas
Range	Formula
N2:R2	N2	=SUM(COUNTIFS(N3:N19,{1,2,3,4})*{100,50,20,10})/COUNTIF(N3:N19,"<5")
N3:R19	N3	=IF($M3="","",IF(COUNTIFS($B:$B,$M3,$C:$C,N$1),"Hosts",IF(T3=0,"-",RANK(T3,$T3:$X3))))
T3:X19	T3	=SUMIF($B:$B,$M3,F:F)

 

[stefanaalten]

Many thanks again to both Fluff and Snakehips for your solution - that is precisely what I was trying to achieve!

 

[Fluff]

Glad we could help & thanks for the feedback.