Sum of several lookups

[Mazbuka]

Students do 8 subjects out of 16 , the subject choices will differ for each student.

Student 1's grades are input in b4:q4

A lookup table with corresponding points is in A9:B25

The total points are the sum of the best 6 scoring grades, I want a formula in R4 to calculate the points.

At the moment I have a sum(sumifs that gets total points for all subjects

Under, in row 5, I have individual xlookups for each subject & sum the largest 6. This gives the correct answer but using an extra row. Can I combine the two formulas into R4 or is there another method?

(S4 & S5) are the formulas in T4 & T5)

 

[Fluff]

How about

=SUM(TAKE(SORT(XLOOKUP(B4:Q4,A10:A25,B10:B25,0),1,-1,1),,6))

 

[Nugie]

you can use this one SUMPRODUCT(LARGE(point*(A10:A25=point2),{1,2,3,4,5})) "point" define name B10:B25 and "point 2 define name B5:Q5

 

[Fluff]

point 2 define name B5:Q5

Should that be B4:Q4 as the OP wants to get rid of row5?

 

[Nugie]

Should that be B4:Q4 as the OP wants to get rid of row5?

yes, you are correct

 

[Mazbuka]

Yes!! Both work perfectly, thanks so much.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Mazbuka]

A further development needed on this (I thought I'd be able to adjust it myself but struggling) ...

Subject C is awarded an extra 25 points for an A1 to A7 grade

(If anyone is interested this is the Irish University points access system, subject C is maths, A grade is higher level)

 

[Fluff]

As this is a significantly different question, it needs a new thread. Thanks