I am trying to move a spreadsheet from Google Sheets to OnlyOffice. In the Google Sheets sheet I have the following formula : =SUM(FILTER(Data!$G2:$G;REGEXMATCH(LOWER(Data!$B2:$B);“nlziet|npo|spotify”))) Do you know if this will be supported in the upcoming versions. The formula that does the same is SUMPRODUCT : =SUMPRODUCT(Data!$G$2:$G$1000 * ((ISNUMBER(SEARCH(“nlziet”; Data!$B$2:$B$1000))) + (ISNUMBER(SEARCH(“npo”; Data!$B$2:$B$1000))) + (ISNUMBER(SEARCH(“spotify”; Data!$B$2:$B$1000))) > 0))
But as you can see this is quite a big formula. So I am looking something more compacter.
In current versions of ONLYOFFICE Docs there is no REGEXMATCH function with exactly the same name as in Google Sheets, but starting from version 9.3 the spreadsheet editor includes regex functions such as REGEXTEST, REGEXEXTRACT, and REGEXREPLACE. REGEXTEST checks whether the text matches a given pattern and returns TRUE or FALSE, so it works as the equivalent of Google Sheets’ REGEXMATCH.
This means you can rewrite your original formula using REGEXTEST and keep it compact.
So, instead of expanding to a long SUMPRODUCT + SEARCH expression, you can use FILTER together with REGEXTEST in ONLYOFFICE Docs 9.3+.
If you are currently on an older version of ONLYOFFICE Docs where these functions are not available, please update to the current version and try again.
Hope this function covers your needs and makes the transition from Google Sheets to ONLYOFFICE more convenient!