Sports Carnival Scoring

[Kiki1995]

I am hoping to make a scoring sheet for a primary school sports carnival.

My goal is for it to look something like this:

	First	Second	Third	Fourth
Race 1
Race 2
and so on...

First = 20 points, Second = 15 points, Third = 10 points, Fourth = 5 points.

I am hoping to be able to put the faction names in the first - fourth boxes, either by typing or a drop down arrow or however, and have them automatically be given points on a score sheet like this

	Blue / Bilya	Red / Karla	Gold / Djinda	Green / Balga
Race 1
Race 2
and so on...
Total

Where it gets tricky is that in some races one faction can get multiple finish places because we put 2-3 students from each faction into a race so everyone has a turn. So, for example, Blue could place first and third, and would get 30 points for that race.

Is this possible?

 

[FormR]

Hi, welcome to the forum!

You could try something like this..

Book1
	A	B	C	D	E
1	Results	First	Second	Third	Fourth
2	Race 1	Blue / Bilya	Green / Balga	Blue / Bilya	Red / Karla
3	Race 2	Green / Balga	Blue / Bilya	Red / Karla	Gold / Djinda
4	Race 3	Red / Karla	Red / Karla	Blue / Bilya	Red / Karla
5	Race 4	Red / Karla	Red / Karla	Red / Karla	Red / Karla
6	Race 5	Red / Karla	Blue / Bilya	Green / Balga	Green / Balga
7	Race 6
8	Race 7
9	Race 8
10	Race 9
11	Race 10
12	Race 11
13	Race 12
14	Race 13
15
16	Scores	Blue / Bilya	Red / Karla	Gold / Djinda	Green / Balga
17	Race 1	30	5	0	15
18	Race 2	15	10	5	20
19	Race 3	10	40	0	0
20	Race 4	0	50	0	0
21	Race 5	15	20	0	15
22	Race 6	0	0	0	0
23	Race 7	0	0	0	0
24	Race 8	0	0	0	0
25	Race 9	0	0	0	0
26	Race 10	0	0	0	0
27	Race 11	0	0	0	0
28	Race 12	0	0	0	0
29	Race 13	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
B17:E29	B17	=SUMPRODUCT(--(INDEX($B$2:$E$14,MATCH($A17,$A$2:$A$14,0),0)=B$16),{20,15,10,5})

 

[fjns]

Hi, see the linked file for a possible solution...

The formulas used in the table...
B2: =SUM(B3:B12) (Range:B2:J2 ...)
B3: =LET(r,MATCH($A3,Scoring!$A:$A,0),IFERROR(IF(OR($A3="",B$1=""),"",IF(INDIRECT(ADDRESS(r,2,1,1,"Scoring"))=B$1,Param!$B$1,0)+IF(INDIRECT(ADDRESS(r,3,1,1,"Scoring"))=B$1,Param!$B$2,0)+IF(INDIRECT(ADDRESS(r,4,1,1,"Scoring"))=B$1,Param!$B$3,0)+IF(INDIRECT(ADDRESS(r,5,1,1,"Scoring"))=B$1,Param!$B$4,0)),"")) (Range: B3:J30 ...)

SportsCarnival.xlsx

 

[Kiki1995]

Thank you very much FormR and fjns! I really appreciate your help! Both are awesome. I will bring them to my next sports meeting at school and we can decide which to use.

 

[fjns]

Hi, I am very glad that the formulas work.
If I have enough knowledge, I am happy to help at any time.