logic for a simple way to know which column to sum

user_11015642 0
Viewed 48

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: https://docs.google.com/spreadsheets/d/1eRCv4paCEAAufegmzbPCchIErteA-CYYmXp5lNizpO0/edit?usp=sharing

I also need to be able to match the Name column A and the Date in D1.

It seems like there should be a simpler way, but I don't know how to do it.

vlookup google-sheets-formula google-sheets sum sorting
2 Answers

The first part of your question should just be a sumif (although you have to extract the numbers from your headers in the data sheet):

=ArrayFormula(sumif(--regexextract(data!Q1:W1,"\d"),"<"&D4,data!Q2:W2))

enter image description here

You could use index/match to get the correct row from the data tab (here demonstrated using sumifs):

=ArrayFormula(sumifs(index(data!Q2:W,match(A4&$D$1,data!A2:A&data!B2:B,0),0),--regexextract(data!Q1:W1,"\d"),"<"&D4))

enter image description here

user_38949170

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)))

enter image description here

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)))

enter image description here

demo sheet

user_56326290
Related