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!

 

[innmentor]

Hi Fluff apologies for delay but hospitals a'int what they used to be.
Your formulas work a treat have linked the initial data from the main programme where the results are entered and linked the ranking to the name number data and seems to work fine in looking up the 1,2,3,4, named winners ok. Unfortunately there are a couple of ghost which have been generated with high total score I would put money on operator error ( Me). Can I press your wonderful patience a little bit more as I am almost over the last hurdle to making this work for our charity

cheers
Ken

 

[Fluff]

Do you have a formula in the blank cells?

 

[innmentor]

Hi Fluff there are formulas in all the cells including the blank ones showing high scores. Have highlighted and cleared contents of the cells with the exception of the first rowstarting e3. Havee I made another mistake as my head tends to spin a bit using formula I don't understand so please forgive me

K

 

[Fluff]

Ok, change the formula in E3 to

=LET(a,TOCOL(A3:D12,1,1),UNIQUE(FILTER(a,a<>"")))

 

[innmentor]

=LET(a,TOCOL(A3:D12,1,1),UNIQUE(FILTER(a,a<>"")))

Done and still showing blank cell in col e as scoring 86 points Looks lie we have jumped down a rabbit hole and I don't want to take up loads of your valuable time

K

 

[Fluff]

What is the formula in those cells?

 

[innmentor]

e3 =LET(a,TOCOL(A3:D12,1,1),UNIQUE(FILTER(a,a<>""))) f4 =MAP(E3#,LAMBDA(m,SUMPRODUCT((A3:D12=m)*(A2:D2)))) G3 =rank.eq(f3#,f3#)

 

[Fluff]

Not those cells, but the cells in A3:D12

 

[innmentor]

Fluff I thought we were talking about the cells containing the formulas you gave me. The cells a3:d12 are linked to the score inputs from another part of the programme (='result sheet'!F5) where the judges sheets are entered for their positions they have decided so on row 3 they have determined that entrant no 21 was is, entrant 7 was second and entrant 20 was third and no one was 4th.. This is the basic data that I am using to give overall sores in each section. A3 to D12 are for flowers and a15:d39 are vegetables entrants are free to enter whatever classes they want. Hope this helps
K

 

[Fluff]

The cells a3:d12 are linked to the score inputs from another part of the programme (='result sheet'!F5)

That means the cells are not blank, they will contain a 0 if the F5 is blank, so try

=LET(a,TOCOL(A3:D12,1,1),UNIQUE(FILTER(a,a<>0)))