Excel Formula help adding up smallest numbers in 2 different rows

Question

=small(I8:I13,1)+small(I8:I13,2)+small(G8:G13,1)+small(G8:G13,2)

This formula is pulling me the two lowest numbers from I8:I13 and it is adding it to the 2 smallest numbers from G8:G13. How I want it to work is for it to pull the 4 smallest (best) between the two columns but not using the smallest number in both columns if they are both in the same row. In column D8:D13 I have 6 names. The formula needs to pull the 4 lowest numbers, added together but not using the same person in Column D twice. So if row 8 has two of the four smallest numbers, I only want it to pull the best and not use the same person twice.

Answer 1

You could use the following if you're using Office 365: =SUM(SMALL(INDEX(G8:I13,SEQUENCE(ROWS(G8:I13)),{1,3}),SEQUENCE(4)))

Or:

=LET(range,G8:I13,
     columns,{1,3},
     smallest,4,
SUM( 
    SMALL(      
    INDEX(range,SEQUENCE(ROWS(range)),columns),
    SEQUENCE(smallest))))

Or in older versions: =SUM(SMALL(INDEX(G8:I13,{1;2;3;4;5},{1,3}),{1;2;3;4})) (I think it requires being entered with ctrl+shift+enter)