Ask Your Question
0

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

Comments

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
2

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:
{=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.

edit flag offensive delete link more

Comments

1

Alternative formula:

=SUMPRODUCT((E4:E11)*(D4:D11={"complete"."pending"}))
PKG gravatar imagePKG ( 2021-01-08 16:03:59 +0100 )edit
1

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

Stats

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

Seen: 91 times

Last updated: Jan 08