Sum of smallest Gross and Net values without using same person twice-CodePudding

I have 6 players. All players have a gross score and net score. I want to add up the 2 lowest gross scores and the 2 lowest net scores together but we can't use the same person for both.

I currently have players in column D. I have Gross scores in f and Net scores in h. 6 gross scores under Column f and 6 net scores under Column h. I am currently using

SUMPRODUCT(small(f8:f13,{1,2} SUMPRODUCT(small(h8:h13,{1,2}

The issue is the same persons scores are being used in Column f and h. I want it two take the 2 smallest scores from f and do the same with h but not use any Player that was already used in f.

CodePudding user response：

For Beta Channel users:

=SUM(SMALL(F8:F13,{1,2}),SMALL(DROP(SORTBY(H8:H13,F8:F13),2),{1,2}))

CodePudding user response：

This will work in Excel 2013 and later (entered as an array formula if you don't have dynamic arrays):

=SUM(LARGE(H8:H13*NOT(IFNA(IF(MATCH(ROW(1:6),MATCH(SMALL(F8:F13,{1;2}),F8:F13,0),0),1,0),0)),{3,4}),SMALL(F8:F13,{1,2}))

but is very tightly coupled to your having exactly 6 players