working out total scores

[innmentor]

Hi not been here for quite some time but here goes.
Have a 4x 10 grid where numbers (representing participants ) in a competition are entered in position in each row representing an individual discipline. So column a-d represent
positions attained 1st -4th with points 10,8,6,2 for each position So grid is filled in with contest and 34 in a1 24 in b1 no one in c1 or d1 which means if no one entered then a blank is left. I need to create a colunm that gives me the unique numbers in the grid ignoring spaces left and attribute to each entrant the number of points each one has attained to then rank them. Any ideas how I can do it . I have been going round in circles trying to use rank function but I am totally lost- HELP PLEASE!

 

[petertenthije]

For unique numbers I would just create a list with all people, numbers 1 through ~.

For the scores you can use this.

=(countifs($A$2:$A$12,$E3)*$A$2)+(countifs($B$2:$B$12,$E3)*$B$2)+(countifs($C$2:$C$12,$E3)*$C$2)+(countifs($D$2:$D$12,$E3)*$D$2)

For the rank you can use:

=RANK($F3,$F$3:$F$12,0)

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G
1
2	10	8	6	2
3					21	24	3
4	21	7	20		5	30	2
5	5				1	16	4
6	1				7	56	1
7	5			5	20	16	4
8	7	5	21		24	8	6
9	7	24	25		11	8	6
10	7				25	6	8
11	20	7	21
12	7	11	1	21
Sheet6

Cell Formulas
Range	Formula
E3:E10	E3	=UNIQUE(TOCOL(A3:D12,1,1))
F3:F10	F3	=MAP(E3#,LAMBDA(m,SUMPRODUCT((A3:D12=m)*(A2:D2))))
G3:G10	G3	=RANK.EQ(F3#,F3#)
Dynamic array formulas.

 

[innmentor]

sorry attachment not uploaded properly

For unique numbers I would just create a list with all people, numbers 1 through ~.

For the scores you can use this.

=(countifs($A$2:$A$12,$E3)*$A$2)+(countifs($B$2:$B$12,$E3)*$B$2)+(countifs($C$2:$C$12,$E3)*$C$2)+(countifs($D$2:$D$12,$E3)*$D$2)

For the rank you can use:

=RANK($F3,$F$3:$F$12,0)

Thanks both work fine the problem is in getting the unique numbers in col E listed for the formulas to give accurate output. Need the formula for column E to list all the unique nos in the grid A3:D12. I worked those out myself I need a formula to do it automatically as you can seethe counts if returns three readings for E10 to E12 which gives incorrect scores

 

[Fluff]

the problem is in getting the unique numbers in col E listed

But that's what the formula I provided in E3 does.

 

[innmentor]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G
1
2	10	8	6	2
3					21	24	3
4	21	7	20		5	30	2
5	5				1	16	4
6	1				7	56	1
7	5			5	20	16	4
8	7	5	21		24	8	6
9	7	24	25		11	8	6
10	7				25	6	8
11	20	7	21
12	7	11	1	21
Sheet6

Cell Formulas
Range	Formula
E3:E10	E3	=UNIQUE(TOCOL(A3:D12,1,1))
F3:F10	F3	=MAP(E3#,LAMBDA(m,SUMPRODUCT((A3:D12=m)*(A2:D2))))
G3:G10	G3	=RANK.EQ(F3#,F3#)
Dynamic array formulas.

Thanks for the effort but this is what happened when I followed your layout and formulas and tis why I came here as I have been having such results since I started on this problem a week ago I'm on 365 so excel should be working but everything I try does not seem to give me what I need. I thought it was me but something isn't right somewhere

 

[innmentor]

For unique numbers I would just create a list with all people, numbers 1 through ~.

For the scores you can use this.

=(countifs($A$2:$A$12,$E3)*$A$2)+(countifs($B$2:$B$12,$E3)*$B$2)+(countifs($C$2:$C$12,$E3)*$C$2)+(countifs($D$2:$D$12,$E3)*$D$2)

For the rank you can use:

=RANK($F3,$F$3:$F$12,0)

thanks but that isn't an option as the numbers are input as results come in and the points and ranking have to be automatic as they are forwared to a schedule of cups etc.-sorry but my brain hurts as I have gone over this with every formula known to man and AI who have also failed for the solution so I do't think AI will be dominating the world any time soon

 

[innmentor]

But that's what the formula I provided in E3 does.

Hi if you check you will see the unique numbers 21 & 25 off the list which is strange???

 

[Fluff]

You need to clear all the cells below E3, F3 & G3, just leaving the formulae in those 3 cells only

 

[innmentor]

Thanks Fluff seems to be just what I needed but there seems to be a ghost appearing but have hospital appointment this morning will send details this afternoon