0

I have identified by trial and error that this one FILTER function is causing a massive "Calculating Formulas..." delay in my Google Sheet. This is the ONLY cause of the delay I have tested it extensively. MY solution right now is to add a "1" before the equal sign to turn the formula into text so then whenever I need to use the formula to retrieve and save its results I remove the "1" to run it and then put it back and refresh.

Of course this is not a good clean workaround.

Formula is:

=FILTER({FORMULARIO!C9:C}, FORMULARIO!J9:J<>"PAGÓ", FORMULARIO!B9:B=10, FORMULARIO!C9:C<>"", FORMULARIO!A9:A>TODAY()-5, NOT(REGEXMATCH(FORMULARIO!Q9:Q, TEXTJOIN("|", 1, FILTER(FORMULARIO!Q9:Q, FORMULARIO!J9:J="PAGÓ")))))

I'm looking for potential edits to the formula to reduce this humongous delay. Maybe turn it into a QUERY function?

  • Please share a copy of your spreadsheet, excluding any private or confidential information. – Tedinoz Oct 17 at 0:38
0

This QUERY version of the same FILTER function 100% solved the issue thanks to @player0

=QUERY(FORMULARIO!A13:Q, "select C where not upper(J) = 'PAGÓ' and B = 10 and C is not null and A > date '"&TEXT(TODAY()-5, "yyyy-mm-dd")&"' and not Q matches '"&TEXTJOIN("|", 1, QUERY(FORMULARIO!J13:Q, "select Q where upper(J) = 'PAGÓ'", 0))&"'", 0)
  • Please explain why... – user55949 Oct 17 at 9:11
  • I dont know why, it simply did,there is something with thtfilter function that doesnt stop calculating formula – sebas Oct 17 at 15:20

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.