This sum(sumifs()) works in Excel but not Calc. Why not?
This formula, =SUM(SUMIFS(E4:E11,D4:D11,{"complete","pending"})) , works in Excel, adding all numbers in E that have either "complete" or "pending" in the corresponding cell in D. In Calc, the formula only adds numbers meeting the first condition, "complete". If I swap the position of the conditions in the formula, it only adds "pending" numbers. What do I need to change to make it work in Calc? Thanks.
UPDATE: Keme's answer works! So the difference is that Calc needs to be told that the formula is an array, whereas Excel doesn't. Editing the formula as he describes works to convert the formula. But if I were to type the formula as above and enter CTRL+SHIFT+ENTER (instead to just ENTER), the formula is entered as an array formula and the brackets are automatically added at each end. Thanks to all who provided guidance.
Please upload your .ods type sample file here.
My first tip: Use the semicolon (;) parameter separator character instead of the comma (,). The LO Calc is a multilingual software, and the comma (,) is the decimal separator in many languages.
In addition to what @Zizi64 said: choose Tools - Options - LibreOffice Calc - Formula and check the values in the fields in the Separators section. Please note the description of this section in Help.