Summation of Data in cells of mixed Numerical/Text Data

Discuss the spreadsheet application
Post Reply
open-office
Posts: 2
Joined: Sat Nov 30, 2013 12:15 pm

Summation of Data in cells of mixed Numerical/Text Data

Post by open-office »

I have a column of mixed numerical and text data in each cell.
I would to apply a function, =SUM() say, to just the numerical data types of the columns. To sum up all the numerical data in the column.

I've tried doing =SUM(A1:A9) but the result is 0. I've also tried formatting the cells and have the Category as Number and Format as General. The result is still the same.
LibreOffice 3.5.4.2
Build ID: 350m1(Build:2)
Linux 3.10-0.bpo.3-rt-amd64
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Summation of Data in cells of mixed Numerical/Text Data

Post by Villeroy »

ALl your figures are text. menu:View>HighlightValues [Ctrl+F8] displays numbers in blue and text in black. Why do you enter, import or copy text if you want numbers? Formatting attributes must not change the data type of a cell value. Number formats do not apply to text.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
open-office
Posts: 2
Joined: Sat Nov 30, 2013 12:15 pm

Re: Summation of Data in cells of mixed Numerical/Text Data

Post by open-office »

The data types are mixed, Text and Numerical values in the cells of the column.
I would like to find a way to sum just the numerical values in the cells.

I have other columns which have more strict data types but in this case I want to find a way to make the best use of the data I already have.

Is there a counter solution around this problem?
LibreOffice 3.5.4.2
Build ID: 350m1(Build:2)
Linux 3.10-0.bpo.3-rt-amd64
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Summation of Data in cells of mixed Numerical/Text Data

Post by Villeroy »

SUM works for me. It sums up the numbers and disregards the text.
If you want to sum up all data that are either numbers or numeric text, you've got to convert the text to numbers. The VALUE function may do so or not depending on your office locale and on the aleged locale of the concrete text values.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply