0

I was hoping someone can help optimize this spreadsheet, where I need to QUERY a dataset while filtering by the cumulative sum of a column by certain thresholds.

Here's the spreadsheet if you want to take a look but I just want to incorporate the columns in yellow in the query, so I just have to use one formula. The query result would be only the rows where Column O "in threshold" is "Yes": enter image description here

0

See Test File

Formula in Q2 returns check to threshold conditions, also I separated those conditions in table in range S2:U4.

Formula calculates aggregate shares for current row

SUMIF(SEQUENCE(COUNT($M$2:$M),1,1,1),"<="&SEQUENCE(COUNT($M$2:$M),1,1,1),$M$2:$M)

and compares to threshold conditions table with VLOOKUP() formula.

Same repeats for previous row values. Also there is additional check for cases when new country appears (second part of formula).

  • Awesome, thanks Oleg! Never thought about using SEQUENCE. Appreciate the help! – Bob Sep 24 '20 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.