Ask Your Question

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

asked 2021-01-08 01:20:44 +0100

Chuck1991 gravatar image

updated 2021-01-08 19:44:27 +0100

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.

edit retag flag offensive close merge delete


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.

Zizi64 gravatar imageZizi64 ( 2021-01-08 07:08:26 +0100 )edit

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.

JohnSUN gravatar imageJohnSUN ( 2021-01-08 09:54:41 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2021-01-08 14:38:49 +0100

keme gravatar image

updated 2021-01-08 15:43:46 +0100

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:

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.

edit flag offensive delete link more



Alternative formula:

PKG gravatar imagePKG ( 2021-01-08 16:03:59 +0100 )edit

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

erAck gravatar imageerAck ( 2021-01-11 21:38:19 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-01-08 01:20:44 +0100

Seen: 91 times

Last updated: Jan 08