A numeric string typed inside SUM works in Excel but errors in LibreOffice
When you type a number as text directly into SUM's argument list, such as
=SUM(1,"2",3), Excel converts the "2" to 2 and returns 6. LibreOffice
Calc returns #VALUE! for exactly the same formula.
The surprise
A quoted numeric literal passed straight into SUM is coerced to a number by Excel but rejected by LibreOffice. This is not about text sitting in a referenced cell (both engines ignore that); it is specifically about a text literal handed directly to the function.
A minimal example
| Formula | Excel (documented) | Google Sheets | LibreOffice Calc 25.8.7.3 (executed) |
|---|---|---|---|
| =SUM(1,"2",3) | 6 | Not yet executed | #VALUE! |
The 6 is the documented-expected value recorded in our test corpus; the #VALUE!
is what our harness computed by recalculating the workbook in LibreOffice Calc 25.8.7.3. The plain
=SUM(1,2,3) without quotes returns 6 in every engine; only the quoted literal
diverges. We have not yet executed this case in Google Sheets, so that column stays honest.
Why it happens
Microsoft's SUM documentation states that "logical values and text representations of numbers
that you type directly into the list of arguments are counted," and separately that text inside a
referenced array or range is ignored. So a quoted numeric literal handed straight to SUM is a
value Excel promises to coerce. LibreOffice does not coerce a text literal argument to a number
here; it raises #VALUE! instead. See Microsoft's
SUM function reference.
How to migrate safely
Remove the quotes so the argument is a real number:
=SUM(1,2,3)
If the text is unavoidable (for instance it is stitched in by a generator or comes from an import), convert it explicitly so both engines agree:
=SUM(1,VALUE("2"),3) or =SUM(1,--"2",3)
Best of all, reference cells that hold real numbers rather than embedding literals in the
formula. Text that lives in a referenced cell is ignored by both engines, so
=SUM(A1:A3) over a column that mixes numbers and stray text behaves the same in Excel
and LibreOffice; only the typed-in quoted literal forks.
This pattern most often appears in machine-generated formulas, in values pasted as text, and in
files exported from other tools. Before migrating a workbook, scan its SUM formulas for quoted
numbers in the argument list, because Excel will have quietly absorbed them into the total while
LibreOffice will surface a #VALUE! the moment the file recalculates.