Google Sheets – Conversion from LibreOffice

formulasgoogle sheetsgoogle-sheets-arrayformularegex

I use LibreOffice to create xlsx files. There are some formulas to test conditions. Everything is ok under LibreOffice but when I open the file with Google Sheets formulas give the wrong result.

e.g. the following formula

=IF(C32="","",IF(COUNTIF(C6:C28,"SA|PA|MA|PB|MB")=C32,"0",COUNTIF(C6:C28,"SA|PA|MA|PB|MB")-C32))

gives activates the second COUNTIF as if was an error.

Some explanations:
C32 = 5
C6:C28 contains 5 of the required pars (SA|PA|….)

So I expect the value of 0 but I receive a -5 result. Hope I was clear.

Best Answer

=ARRAYFORMULA(IF(LEN(C32), 
 IF(SUM(N(REGEXMATCH(C6:C28, "SA|PA|MA|PB|MB")))=C32, "0",
    SUM(N(REGEXMATCH(C6:C28, "SA|PA|MA|PB|MB")))-C32), ))

0

alternatives:

=IF(C32<>"", IF(COUNTIF(C6:C28, "SA")+
                COUNTIF(C6:C28, "PA")+ 
                COUNTIF(C6:C28, "MA")+ 
                COUNTIF(C6:C28, "PB")+ 
                COUNTIF(C6:C28, "MB")=5, 0, COUNTIF(C6:C28, "SA")+
                                            COUNTIF(C6:C28, "PA")+ 
                                            COUNTIF(C6:C28, "MA")+ 
                                            COUNTIF(C6:C28, "PB")+ 
                                            COUNTIF(C6:C28, "MB")-C32), )

=ARRAYFORMULA(IF(LEN(C32), 
 IF(SUM(COUNTIF(C6:C28, {"SA"; "PA"; "MA"; "PB"; "MB"}))=5, 0, 
    SUM(COUNTIF(C6:C28, {"SA"; "PA"; "MA"; "PB"; "MB"}))-C32), ))

Related Topic