This sum(sumifs()) works in Excel but not Calc. Why not?

[Chuck1991]

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.

[Zizi64]

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.

[JohnSUN]

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.

[keme]

The set of allowed values is an array, so the formula needs to be entered as an array formula to work.

• Push F2 to edit the formula.
• Make a mock edit (space, then backspace) so the formula is parsed anew upon entry.
• Enter with ctrl+shift+Enter

If successful, the formula will appear in braces to signify array mode, like so:

{=SUM(SUMIFS(E4:E11,D4:D11,{"complete","pending"}))}

This also works in Excel. For this formula, Excel will assume array evaluation from context, even without creating an explicit array formula. Forcing array evaluation is still recommended, and required if you want to make the formula (and your file) portable.

[PKG]

Alternative formula:

=SUMPRODUCT((E4:E11)*(D4:D11={"complete"."pending"}))

[erAck]

Fwiw, this sounds like tdf#133260, have inline arrays propagate array mode to caller, which got implemented for LibreOffice 7.1