Description: (Why can't I attach a spreadsheet file that demonstrates the problem?) If a numeric cell is actually stored as a text cell, then its value is not included in a SUM() along with other numbers. This is expected. If there is a SUM() and the column contains ONLY text cells with numbers in them, then it DOES calculate their total. This is not expected. Steps to Reproduce: B3=100 C3=120 D3=B3-C3 (shows -20) B4='100 C4='120 D4=B4-C4 (shows -20) B5=100 C5=120 D5=B5-C5 (shows -20) B9=SUM(B3:B5) (shows 200) Actual Results: The cell B4 is a text cell. It should not be included in the calculation for D4 Expected Results: Not include text cells in SUM() formula. Reproducible: Always User Profile Reset: No Additional Info: Display an error or not include the value in the calculation.
Created attachment 176153 [details] Spreadsheet has strange SUM behaviour
That happens when automatic conversion of text to numbers is on for operands (and possibly even with locale dependent conversion). To get rid of that behaviour switch it off under Tools -> Options (or on Mac Preferences) -> Calc -> Formula, Detailed Calculation Settings, Custom, Details..., Conversion from text to number: best select "Generate #VALUE! error". Unfortunately a gazillion of Excel users expect the bad current default setting. See also https://help.libreoffice.org/7.2/en-US/text/shared/optionen/detailedcalculation.html?System=UNIX&DbPAR=CALC