Google Sheets: Add values based on top 2 numbers in separate column

Spent 2 days racking my brain on this one so I figured I'd seek some help.

I need to add values from a table based on the rank of other values in a separate column.

In my example, F2 and I2 would be added because H2 and K2 are the largest values among {E2,H2,K2}

Ultimately, B2 will be (F2+I2)/(G2+J2)

I've been able to get H2+K2 using SUMPRODUCT(LARGE({E2,H2,K2},{1,2})) but haven't figured out how to report the appropriate columns to the left.

*N is numerator, *D is denominator, *S is the score derived from *N/*D

Not sure if it's worth noting that each cols C:K are provided via VLOOKUP that reference different tables for each set of values (e.g. AN,AD, and AS all come from Table A)

Any help is appreciated!

One option:

=SUMPRODUCT(LARGE(IF(RIGHT(E1:K1)="S",C2:I2),{1,2}))/SUMPRODUCT(LARGE(IF(RIGHT(E1:K1)="S",D2:J2),{1,2}))

Oldere and Falcondude,

These work for the example I gave, however, if the values in C2 and D2 were 1.9 and 2 respectively, the result should be different because .95 (E2) is larger than .8 (H2)

Try:

=SUMIFS(C2:I2,E1:K1,"*S",E2:K2,">="&AGGREGATE(14,6,(RIGHT(E1:K1)="S")*(E2:K2),2))/SUMIFS(D2:J2,E1:K1,"*S",E2:K2,">="&AGGREGATE(14,6,(RIGHT(E1:K1)="S")*(E2:K2),2))

I'm sure this works, just not with gSheets. Is there a way to maybe use INDEX and FILTER to accomplish the same thing? My string at the top is also two words (Cox Score). In you previous formula I substituted "S" with "e" and it was fine...I think.

I've updated my sample sheet to include all the basic info I'm working with. I apologize for not doing this sooner. Honestly thought I could figure it out with just a little direction but that is not the case. Definitely a case of not knowing what I don't know.

Janmorris, when I blew this up to fit my data it gave me #VALUE.

It needs to calculate the sum of "NUM" divided by the sum of "DEN" for the top 4 "SCORES". If there aren't 4 "SCORES", it would accept less. In my true sheet, the "X" is generated instead of #N/A but I can make it display anything if it's easier. Column C can be filtered to display rows with 4 or more scores.

Again, sorry for being a noob. I sincerely appreciate the help.

Updated file: is in previous post by Janmorris. Since I'm a noob I can't insert links into my posts yet lol.

Janmorris, that's the ticket!! Thanks so much.

I am very much guilty of the XY thing, without even intending to, which I think is the point in the article. The article was very insightful and I appreciate the referral.