hi all,
when i do
=SUM(E1+E2+E3+E4+E5+E6+E7+E8+E9+E10+E11+E12)
it works fine
but when i do
=sum(e1:e12)
it doesnt work and got no idea why
thanks,
rob
Your version (4 levels like 24.2.2.2) of LibreOffice?
Your OperatingSystem and its version?
Did your sheet(s) have part of their CV saved to an alien âformatâ like .xls?
Why did you post the first formula in upper case, but the second one in lower case?
Please attach an example file showing the issue for you.
upper lower case doesnt make a difference
ive converted the spreadsheet to xls but same thing happens when i save is as ods
i have windows 11 x64 and libre office is 24.2.3.2
Are you sure? Do you really think that dear colleague @Lupp asked to clarify the difference in writing forms out of simple curiosity? Once again: in your table is the second formula written in capital letters?
as said doesnt make a diference, when i do
=sum(e1+e2)
it does the sum as well it converts the non caps to caps
-
Upper or lower case shouldnât make a difference.
But if you are asking a question and searching help you should be precise - at least if you hope for a useful answer. - If actually lower case is shown in the cell with the formula, this would indicate most likely that the cell was preset to
"Text"(code @) as its NumberFormat. - If you arenât precise about what you did, I might also suspect you entered a leading space in front of the
"="e.g. - ⌠check for user profile corruption
is fairly easy to reproduce:
Version: 24.2.3.2 (X86_64) / LibreOffice Community
Build ID: 433d9c2ded56988e8a90e6b2e771ee4e6a5ab2ba
CPU threads: 8; OS: Windows 10.0 Build 22631; UI render: Skia/Raster; VCL: win
Locale: de-DE (de_DE); UI: de-DE
Calc: threaded
1 Like
My numbers are text (you can see by the left alignment in the cell) and SUM ignores text!
How is it with you?
2 Likes
didnt work when i formatted the column into text (this includes all the values i want to add plus the sum cell)
i even did a find/replace all pound ÂŁ signs as i thought that was messing with it but same results
i will just + all the cells in that case as its only 12 cells so not that bad
If you neither must nor want to understnd things, you can use =SUMPRODUCT(VALUE(E1:E12)) .
This way you can get a sum as well including numbers as ânumeric textsâ.
You will, however get an error if a text is contained in the range which isnât recognized as numeric under your settings (locale, decimal separator, currency symbols, âŚ). SUM() simply ignores such content.
1 Like
What language is this (2 posts above) supposed to be?
???
it worked!! thank you 
its english UK language, how would i find this out
The colon is working just fine and as intended. It causes a range of cells to be sent to the SUM() function for evaluation. The cause of your issue is, in a manner of speaking, tied to âŚ
⌠data type conversion and order of operations
The SUM() function adds up any numbers given to it, either by cell references (single cells or ranges), inline number, or formula. It will disregard anything that evaluates to text.
The + operator assumes that both operands are numbers, and attempts to interpret text operands unambiguously as a number.
In other words
- When you specify
E1+E2+E3+E4+E5+E6+E7+E8+E9+E10+E11+E12, you insist that âthey should all be numbersâ. The calculation is evaluated numerically before it is passed as a single number result to theSUM()function, so in this case SUM() receives a proper number⌠- SUM will not make the same assumption as the plus operator. When you specify range
e1:e12as input top the SUM() function, you suggest that âThere may be numbers in the range. Please add them up.â Any text cells (even when they look like a proper number) will be disregarded.
Solution, maybe
The VALUE() function takes a text argument and interprets it numerically. If given a cell range, it takes the text from a single cell in the range. To circumvent this limitation, you can enter it as an array formula (hold CtrlShift when you press Enter). Successful array formula entry will add braces around the formula.
The full formula you need is then this:
=SUM(VALUE(E1:E12))
When entered as an array formula, cell should display formula like this:
{=SUM(VALUE(E1:E12))}
Note: You donât type the braces. They are viewing artifacts added by Calc to indicate the array formula state.
Note also: Luppâs suggestion to use SUMPRODUCT() does exactly the same, with a different approach. The SUMPRODUCT() function implicitly works in array mode.
I just realized that you responded to that suggestion that âit workedâ. You would perhaps also tag his response there as the solution (tick box below his comment).
any documentation / FAQ / tests for that trick ?
What is it that you consider a trick?
well, canât see anything about a difference of âinterpretationâ for operators : Operators in LibreOffice Calc
except here for string concatenation with â+â
https://wiki.documentfoundation.org/Documentation/BASIC_Guide#Operators
whereas the description of SUM() arguments is pretty extensive : Documentation/Calc Functions/SUM - The Document Foundation Wiki
- A real number, or an expression that evaluates to a real number
⌠- Text in cells and empty cells are ignored.
quite puzzling why operator arguments are handled differently from numerical function arguments ⌠
Operators typically work on one explicit cell at a time, so it makes some sense that the formula is interpreted in such a way: =A+B insists that both A and B be numeric.
This spreadsheet syntax differs from BASIC syntax, where in string context, the + is âoverloadedâ as a concatenation operator. This may be unfortunate, but it is the way things work.
Having a different behavior for functions allows for comments and other textual entry within a summed column, without breaking the summation. This also makes some sense (albeit often a messy practice).
Not sure whether this difference in behavior is documented officially and explicitly anywhere accessible. It can be tested easily. Just enter numbers as text (leading apostrophe, or preformat cell range as text) in a few cells, and see how different calculation approaches differ in result.
The array formula works the same in Calc (LibreOffice and Apache OpenOffice), MS Excel and Google Sheets. When entered as a plain formula, Excel still returns the sum (in a way implying array context) while the others will extract a âcorrespondingâ element from the source data (considering only same row).
The difference in behavior may be important if you share spreadsheet models with others who use different tools. The implied array context of Excel can be explicitly stated as explained above. The implied "relative lookup" in Calc/Sheets can be explicitly stated e.g. using OFFSET(). Different tools imply different intentions from ambiguous formulas. Explicit formulas avoid this ambiguity.
ok. so the distinction is not operator vs function
e.g. =ABS(" - 7.") gives 7
is it at least in the code ?
any blame to refer to a specific commit / bugzilla ?