Adding the 3 greatest values from selected cells without duplicates?!

[Mitchell Arthur]

Good Afternoon all,

Firstly, im not sure this is possible but its worth a shot!

I have a spreadsheet which has multiple rows of people and their exam score data. Each students final mark is their 3 highest scores however there is a certain combination of the scores required.

The combination has to be Highest Team Sports score + Highest Individual Sports score + Next highest team OR individual sports score. I have attached a simple picture below for reference.

Persons A score should total 25 and Persons B score should total 30.

Due to the depth of my real spreadsheet which holds data for over 150 students if this score was generated automatically every time scores got updated my life would be much much easier!

If anyone can help, i will forever be in your debt!

Many Thanks

Mitchell

 

[Eric W]

While pondering a bit more, I came up with a shorter version:

=SUM(LARGE(AGGREGATE(14,6,H3:BP3/ISNUMBER(SEARCH("team",F$1:BN$1)),{1,2})*{1,1;0,0}+AGGREGATE(14,6,H3:BP3/ISNUMBER(SEARCH("individual",F$1:BN$1)),{1,2})*{0,0;1,1},{1,2,3}))

Same basic idea, but I used some array manipulation to get rid of two of the AGGREGATES.

 

[Mitchell Arthur]

Thanks all!

Managed to make it work on my spreadsheet! Appreciate all the advice!

Only a matter of time before you hear from me again!

 

[Eric W]

Glad we could help! ?