logic for a simple way to know which column to sum-CodePudding

I am using google sheets to look at some gps speed and distance data and I want to sum up all of the distance over 75% of max speed. Column D has 75% of max speed. Columns Q-X has distance at 2 m/s , 3 m/s, 4m/s, etc. Other than doing a massively nested if statement is there an easier way to do this?

Otherwise I am thinking it will look something like: =if(d3>9,sum(q3:x3),if(d4>8,(sum(q3:w3),if(d4>7,(sum(q3:v3),if(d4>6,(sum(q3:u3),if(d4>50,(sum(q3:t3),if(d4>40,(sum(q3:s3),if(d4>30,(sum(q3:r3),if(d4>20,q3,0)))))))

Here is a link to the data:

CodePudding user response：

B4:

=INDEX(SORTN(SORT({data!F2:F, data!A2:A}, data!F2:F, 0), 9^9, 2, 2, 1),,1)

C4:

=INDEX(SORTN(SORT({(data!F2:F*0.75), data!A2:A}, data!F2:F, 0), 9^9, 2, 2, 1),,1)

D4:

=INDEX(SORTN(SORT({(data!F2:F*0.75)/2.237, data!A2:A}, data!F2:F, 0), 9^9, 2, 2, 1),,1)

E4 for all time top:

=ARRAYFORMULA(IF(A4:A="",,MMULT(IFERROR(VLOOKUP(A4:A, 
 SORTN(SORT({data!A2:A, data!Q2:X}, data!F2:F, 0), 9^9, 2, 1, 1), 
 IF(INDEX(SORTN(SORT({(data!F2:F*0.75)/2.237, data!A2:A}, data!F2:F, 0), 9^9, 2, 2, 1),,1)>
 SEQUENCE(1, 8) 1, SEQUENCE(1, 8) 1, 0), 0), 0), SEQUENCE(8)^0)))

or E4 for date selected top:

=ARRAYFORMULA(IF(A4:A="",,MMULT(IFERROR(VLOOKUP(A4:A, 
 SORTN(SORT(FILTER({data!A2:A, data!Q2:X}, data!B2:B=TEXT(D1, "mm/dd/yyyy")), 
 FILTER(data!F2:F, data!B2:B=TEXT(D1, "mm/dd/yyyy")), 0), 9^9, 2, 1, 1), 
 IF(INDEX(SORTN(SORT({(data!F2:F*0.75)/2.237, data!A2:A}, data!F2:F, 0), 9^9, 2, 2, 1),,1)>
 SEQUENCE(1, 8) 1, SEQUENCE(1, 8) 1, 0), 0), 0), SEQUENCE(8)^0)))

demo sheet