This seems to be an Excel speciality nonsense.
Apparently the cell range F1:F4 is forced into an array evaluation for --(F1:F4) without even entering the =SUM(--(F1:F4)) formula as array mode formula. Hence when in Calc for the formula on any row other than in 1:4 there is no implicit intersection with F1:F4 and the result is #VALUE! (in a row of 1:4 it would be the value of the one intersected cell of F1:F4).

Effectively --(F1:F4) should be the same as -(-(F1:F4)) or --F1:F4 but likely Excel treats them different and =SUM(-(-(F1:F4))) and =SUM(--(F1:F4)) and SUM(--F1:F4) would not all produce the same results.

I'm against implementing this unless it would be clearly specified by the ODF OpenFormula (ODFF) standard.

The info here is slightly inaccurate.

Excel uses the double unary as one of the ways to coerce boolean TRUE/FALSE values into numeric values. Nothing new.

Calc doesn't need to coerce boolean values. Nothing new.

Calc supports the double unary just fine for traditional "legacy" pre-dynamic-era functions, such as SUMPRODUCT().

With dynamic functions, Excel now can have functions such as SUM() supporting "array" arguments as a non-array function, which were not supported in the same way in the past, when you needed to introduce them as ("legacy") array formulas with CSE.

This is why it would seem that using 
=SUM(--F1:F4)

...is something that Calc does not support. Well, it is not really because of the double unary (aka double negative), but because of the new so-called "dynamic" arrays, functions and formulas in Excel.

I guess that, in some sense, this is one more RFE for Calc to support "dynamic" array functions.

(In reply to ady from comment #2)

Sorry, typo:
> This is why it would seem that using 
> =SUM(--F1:F4)

That should be =SUM(--(F1:F4)).

It would be good for import/export, not necessary internally.

On the other appendage, some sort of a “strict type check mode” to catch mistakes better would not be a bad idea, but it requires more support.
There already are format checks including ISLOGICAL(…).
So what’s left is adding some explicit conversion functions for the rest of conversions as an equivalent of TEXT(…) / NUMBERVALUE(…) / N(…) and type filter functions as an equivalent of T().
So maybe just add some explicit function for boolean-to-number type conversion. In the lax type check mode it’s redundant, but for now it would serve as a simple and unambiguous way to import/export that odd Excel operator once an equivalent function exists.

>  =SUM(--(F1:F4)).
I just tried this. This returns 1 if the cell in F on the same row (F1 for A1) is TRUE, 0 if F1 is FALSE… and F1 if F1 is a number, ignoring the rest of that range,
 that is if F1:F4 = [99 1 2 3] it returns
 99 if in A1 or B1,
 1 in A2 or B2,
 2 in A3 or B3,
 3 in A4 or B4;
 error in A5 or B5,
even though the formula was copied exactly (without allowing it to auto-adjust relative addresses). It’s not an array formula and there’s no highlights, other than of F1:F4 on edit per reference from the formula. Is this the expected behaviour somehow?

Version: 7.5.3.2 (X86_64) / LibreOffice Community
Build ID: 50(Build:2)
CPU threads: 2; OS: Linux 5.4; UI render: default; VCL: x11
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 4:7.5.3~rc2-0ubuntu0.18.04.1~lo1
Calc: threaded

(In reply to TBeholder from comment #4)
> >  =SUM(--(F1:F4)).
> It's not an array formula

That's the point. For versions of Excel that support dynamic arrays, that _is_ an array; not in the traditional "legacy" CSE way.

As already said, =SUMPRODUCT(--(F1:F4)) already supports this, because the function does not need to be introduced with CSE.

In older versions of Excel (which do not support "dynamic" functions) the same =SUM(--(F1:F4)) formula would fail too, in the same way as in LO.

LO should be more precise regarding which features (e.g. "dynamic arrays") to support, in which context, in which versions. Otherwise, users would report things like "LO works fine with this particular function or feature but it fails in such and such context; LO is broken".

I'm not saying this request should be completely rejected; I'm saying the "feature" doesn't seem to be defined in precise terms. How is Calc currently importing "dynamic" formulas from XLSX files? Is there some definition for "dynamic arrays" in some ODF(F) extension, or in the standard itself? If there isn't, then IMHO _that_ should be developed/defined first (and sooner rather than later, because, with time, these requests are probably going to be more frequent).