Convert formula from LibreOffice Calc to Google Sheets

[paparucino]

Hi,
Im sharing some LO Calc files with colleagues which use google sheets.
In Calc everything works perfectly but when I use sheets some formulas give wrong result
e.g. cell H31 contains value 6, the first countif gives 6 as result and I get an underscore sign. This is what I see in Calc

=IF(H31="","",IF(COUNTIF(H6:H26,"P|PA|PV|M|MA|MV")=H31,"_",COUNTIF(H6:H26,"P|PA|PV|M|MA|MV")-H31))

In sheets this is not true and I obtain the value of H31 as result of the second countif
After some researches the only thing closer to my wishes is

=IF(F31="","",IF(COUNTIFS(F6:F26,"P",F6:F26,"PA",F6:F26,"PV" )=F31,"socc","diff"))

But the final result is “diff” (just to be short)
I suppose sheets doesnt recognise the logical “OR” ( or Im not able to find the correct formula)
Any idea?
Thank you in advance
papa

(@Lupp only edited the subject: clarification, better chance to be found by users having in mind a similar question.)

[Lupp]

Concerning standard functions there are specifications approved by OASIS which are regarded by LibO. Since there are no better or more clear standards, and the ODF way is basically even compatible with recent MS Excel as far as formulas are concerned, the only way to tidy up is that either Google also apply these standards or withdraw their GSheets.
There is, however, one basical shortcoming of Excel: It is inapt (even not trying) concerning RegularExpressions, and I suppose, it’s the same with GSheets. If developing with Calc for Excel, and most likely also for GSheest, you need to disable RegEx also for Clac. Done so the critical formulas will also not work as expected in Calc, and you know you need to find a surrogate.
Concerning your formulas COUTIF() is the afflicted standard function.

[mariosv]

Calc’s formula use pipe ‘|’ an ‘or’, but in the second formula every condition in COUNTIFS it’s an ‘and’.
But if I’m not wrong regular expressions are supported by google sheets Syntax for Regular Expressions.
I guess calc formula should work in sheets, Please attach a sample file for test.

[paparucino]

How can I attach a sample?

@mariosv Problem is that the first part of the formula returns false even if COUNTIF is = to ref cell (H31). This is true even if I dont enable RegEx as @Lupp wrote

[erAck]

regular expressions are supported by google sheets

I doubt that (apart from that there is a REGEXMATCH() and two other regex functions), AFAIK there’s no way to switch between regular expressions and wildcards and I think only the simple Excel wildcards are supported in formula expressions but not regular expressions. e.g.

• =SEARCH("b*";"abc") result 2
• =SEARCH("b.";"abc") result #VALUE!

[mariosv]

Maybe in this case using an inner array could work {P|PA|PV|M|MA|MV} changing the separator to what is set up in calc formula options. I didn’t test.

[Lupp]

Imo there is one really open document standard concerning office-type software, and google should

1. apply it thoroughly and completely
2. take part in its further development.
   Nobody needs another competitor playing the incompatibility strategy to defeat others.
   (Yes. I heard of the “google summer of code” and the like. Suspect it to be a trap.)
   The will peobably not follow my advice if not they feel a little bit of force moving them in that direction.