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.

A minimal example

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. Google Sheets was executed too — Drive import, 2026-08-29 — and returns 6, matching Excel’s documented coercion. Excel for the web — a separate application from the desktop product, and the third engine we execute — returned 6 as well when we recalculated the corpus on OneDrive on 2026-09-01, so the documented coercion is a measured behaviour in a Microsoft engine too. LibreOffice is the only one of the four columns that errors.

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 every engine agrees:

=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 everywhere, so =SUM(A1:A3) over a column that mixes numbers and stray text behaves the same in every engine here; 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.

Check before you migrate

A note on which Excel this is. The Excel column in the tables above is Microsoft’s documented behaviour for desktop Excel, as recorded in our test corpus — we do not run desktop Excel, and no value in that column is a measurement. Excel for the web is a different application with its own calculation engine, and that one we do run (recalculated on OneDrive, 2026-09-01). Its measured results are published on each function’s own page rather than in these guide tables. Because we have no desktop run to compare against, a disagreement between an Excel-web measurement and the documented column is genuinely ambiguous: it may mean the web engine diverges from the desktop one, or that the documentation is wrong about both. We do not claim to know which.