[Solved] Sum function returns zero in imported XLS

[leefieux]

Why is this not adding up? The formula is: =SUM(E1:E24)
I've tried plus before the formula. Still gives me zero.

2025-01-02_11h24_40.jpg (24.98 KiB) Viewed 1764 times

Does Calc not recognize parentheses?
See atached.

[FJCC]

The values are probably text. Select the menu View -> Value Highlighting. Text will be black and numbers will be shown in blue.

What do you mean the data were imported? What steps did you take to get the data into OpenOffice? If you type ($220.00) into a cell, what is the result?

[MrProgrammer]

Sum function isn't working in imported XLS

Perhaps the file was originally created in Excel and the values were stored as text by that software. If so, they should remain as text when Calc opens the spreadsheet. I cannot provide advice about using Excel, however I have read that Excel treats text as numbers in some situations, and this had lead to erroneous results which have invalidated scientific studies. If you need text which looks like a number to be treated as a number, perhaps Excel better meets your needs. Calc is not a clone of Excel. In Calc the values should be numeric if you want to perform calculations with them.

Does Calc not recognize parentheses?

In my locale, English (Canada), parentheses tell Calc that the value is negative. You did not indicate your locale. It's possible that not all locales recognize this convention. There are hundreds of locales and I am not familiar with how the others operate.

Why is this not adding up? The formula is: =SUM(E1:E24)

The SUM function ignores text. You must ensure that your cells contain numeric values. If all the cells contain text the sum will be zero. To convert text to numbers you must use the Text to Columns feature. In my locale this feature converts the text value ($50.00) to the numeric value -50. That value can be formatted to display as ($50.00) in the cell. Read section 2. Controlling how data is displayed in Ten concepts that every Calc user should know.

If you need any additional assistance attach a spreadsheet demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach a spreadsheet document.

OpenOffice 3.1 on Windows Vista / NeoOffice 2.2.3 with MacOS 10.4 / OpenOffice 2.4 on Ubuntu 9.04

None of this softare has been supported for at least a decade. This makes me think that your real software environment is something else and your signature is bogus. Please fix this before I help you again, or state that, yes, you really do use all this ancient software.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[leefieux]

Mr Programmer....thank you for your reply.
I import the spreadsheet from my banking site. It exports an XLS spreadsheet of that month's statement. I copy them into my Budget Xls and I get a sum of each month.

I have another problem. When I try to reformat that column, no matter what I select it doesn't change. I even tried clearing the formatting. It doesn't change.

This is mind numbing, lol. I have been working with spreadsheets for 30 years.

[leefieux]

How are you quoting my message? I don't see that option.

I right clicked on the cells, but 'properties' is not an option. I can't tell whether it is text or number.

[leefieux]

This what I see when I click on Format cells. It says it is a number. My titles say they are text. But I still can't change the formatting on anything. I'm lost

[Villeroy]

No formatting converts wrong text into correct numbers. Import your data correctly. Check option "Detect special number" and select the right language, possibly English (USA),in the import dialog.

[leefieux]

Villeroy............Thx, but always exported as numeric to XLS. It says it is numeric in the original XLS. It shows it as numeric in Calc. Nowhere does it say it is text.

All I ever did before was cut and paste and then sum in my XLS. How do I import it 'correctly'?

[Hagar Delest]

Can you upload an excerpt of the file? Keep only few lines and change the labels. See: How to attach a document here.

To quote a message, use the quote button top right of the message you want to quote. Then cut the part that is not needed in the quote.

[leefieux]

Thank you. Never noticed those quote marks before. Here is the XLS.
As stated, copied and pasted from XLS exported from my one bank account.

[RoryOF]

All the $ amounts are preceded by a single quote, which indicates they are text.

[leefieux]

I see that. How can I copy from a Spreadsheet and have them register as numbers in Calc? I never had to do that before. They pasted as numbers.

Ths

[leefieux]

I see what I did. I was playing around with formatting and I guess I left it as text.
Here is another Test.xls with them formatted as numbers as imported from my bank. Sum still won't work.

[robleyd]

Again, most of column E is text as shown by View | Value Highlighting. In this case they are specifically formatted as text.

Note that changing the format does not change the content of the cell, e.g. from text to number. If you try the Text to Columns as suggested above by MrProgrammer you'll find, as he states, that the values will be converted to raw numbers which you can then format (change the way they are displayed) as decimal currency.

[Hagar Delest]

In LibreOffice, I just copied the cells and pasted as RTF and it recognized all the cells as numbers (right):

Calc_rtf.png (18.87 KiB) Viewed 1403 times

Please add [Solved] at the beginning of the title in your first post (top of the topic) with the ✎ button if your issue has been fixed.

[FJCC]

In OpenOffice I found that the cells in E1:E24 are formatted as Text when I select Format -> Cells. I changed that to the Category Currency and the format that is displayed as -$1234.00, which has the format code [$$-409]#,##0.00;[RED]-[$$-409]#,##0.00. I then selected E1:E24 and chose the menu item Data -> Text to Columns. I set Separated By to a tab. I could see all the cells displayed in the lower part of the dialog. I clicked OK and the data were reentered so that the new format was applied. The sum formula then showed ($3,795.89).

[leefieux]

Why does it change numbers to text? XLS never did.

[robleyd]

Can you provide a sample of the source data direct from the source (bank)? And please describe exactly the step(s) you use to get the data into Calc.

[Hagar Delest]

I import the spreadsheet from my banking site. It exports an XLS spreadsheet of that month's statement. I copy them into my Budget Xls and I get a sum of each month.

Do you use the MS Office format (.xls)? First it is rather deprecated and second, if you use AOO/LO, then better stick to their native format (.ods). Any open/save operation in an external format leads to conversion through import/export filters that may lead to glitches because the features are not always the same.
Try another kind of export like csv, your bank certainly offer other possibilities than .xlsx.

[leefieux]

Can you provide a sample of the source data direct from the source (bank)? And please describe exactly the step(s) you use to get the data into Calc.

Provided. Note that E36 has a sum function, where as F36 has e1+e2+e3. F36 works. E36 doesn't. Weird.

[keme]

Note that E36 has a sum function, where as F36 has e1+e2+e3. F36 works. E36 doesn't. Weird.

Not really weird. There is some logic to it. The formulas both work as intended. (Perhaps not as you intended, but as the developers intended that the operations work).

By explicitly adding cell by cell, you implicitly state that they be numeric, so Calc will attempt to interpret textual content as number. SUM() typically operates on a range, and will disregard cell content specifically of text type, regardless of whether numeric interpretation is possible.

The distinction is not perfect. Time/date data is also numeric, and will be accumulated by SUM(). Boolean TRUE/FALSE are numeric one/zero, respectively. Accumulation of those into your sum may be desired, or not. (Note that unlike Calc, MS Excel will distinguish booleans from numeric.)

Bottom line:
Know your tool. Assume responsibility for data integrity and structure. Do not trust that the software (or its developers) reads your mind/intentions correctly.

[leefieux]

I never had this issue with Excel in all the years I used it. How do I fix this in Calc? I need this monthly data summed.

[Hagar Delest]

Strange, the RTF paste special took care of it under LO 24.8 on LMDE but not under Windows 10...
No other option than .csv?
Note that Calc is not a free clone of MS Excel, thus strategy by the developers may be different. You'll have to adapt (if you are to keep Calc).

[leefieux]

I will try ‘paste special’. Tx. No other export option.

I guess I will have to adapt somehow. Thx for your patience and effort!

[RoryOF]

Possibly "import special numbers" when you import your data.

I'm not a Calc expert: I'd pay special attention to the advices of Villeroy, MrProgrammer and Keme about importing your data.

[leefieux]

Thx…I don’t get that option on the import.

[FJCC]

I think the term "import" is causing some confusion. If you are downloading an xlsx file from the bank and opening that with OpenOffice, then you will not get an import dialog. In that case, you can reset the cell format and automatically reenter the data using Data -> Text to Columns as I explained in an earlier post. If you are, or can choose to, download your data from the bank as a csv file, you can open a blank Calc document, choose Insert -> Sheet From File, choose your csv file, and use the Detect Special Numbers option on the import dialog to have the currency values interpreted as numbers.

[leefieux]

I think the term "import" is causing some confusion. If you are downloading an xlsx file from the bank and opening that with OpenOffice, then you will not get an import dialog. In that case, you can reset the cell format and automatically reenter the data using Data -> Text to Columns as I explained in an earlier post. If you are, or can choose to, download your data from the bank as a csv file, you can open a blank Calc document, choose Insert -> Sheet From File, choose your csv file, and use the Detect Special Numbers option on the import dialog to have the currency values interpreted as numbers.

I am sorry...it is 'Exporting' it as a csv file and I am 'importing' it to Calc.

I did what you suggested ' choose Insert -> Sheet From File, choose your csv file, and use the Detect Special Numbers option on the import dialog to have the currency values interpreted as numbers' and it worked. Thx