Why do some Named Ranges break builtins like SUMIFS ? (tilda-concatenation)

clear · February 12, 2026, 8:11pm

Assuming tilda concatenated Named Ranges do not require special syntax in functions like SUMIFS, well, they don’t work with them, yet the Calc Guide makes no mention of it.
.
Tilda concatenation allows non-contiguous ranges in Named Ranges. Calc Guide 26.2, Chapter 15, Named Ranges. FWIW, tilda-cats are allowed by the GUI in Named Ranges but not Database Ranges.
.
Other functions, like SUM, do seem to be able to work with them, always or sometimes.
.
Attached is a 4-sheet ODS that has examples of both proper and broken function. Also attached is a screen grab of the ODS at play. The tilda-cats defs can be seen in the lower right. The second column cells in sheet4 are =formula().

colcat4.ods (12.4 KB)

Lupp · February 12, 2026, 8:33pm

There’s nothing broken.
The reference concatenation by ~ returns a ReferenceList. The SUMIFS() function expects a Reference as its first parameter.
Study Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 4: Recalculated Formula (OpenFormula) Format
https://docs.oasis-open.org/office/OpenDocument/v1.4/
Simple SUMIF() also accepts a ReferenceList.

clear · February 12, 2026, 9:40pm

.
Thanks. Agreed, according to oasis-open.org. But is according to Calc Guide.
.

.
only if the optional third argument is not given
.

https://docs.oasis-open.org/office/OpenDocument/v1.4/part4-formula/OpenDocument-v1.4-os-part4-formula.html#SUM
.

Lupp · February 12, 2026, 10:00pm

My answer had 377 characters.

The images pasted into the posts by the OQ had 236946 bytes.
Ratio>600.

I read the posted texts, but ignored the images.