Equal Sign Sum of Cells Doesn't Work

English
, , , , ,
#1

To get the sum of a series of numbers in cells in a column, I place my cursor inside the cell below the series, then I click on the Equal sign (=) up on the tool bar.

Then I place my cursor in the top cell of the series and drag down to include all the cells.

Instead of getting the sum, I get #VALUE!

When I look at the field for the cell in the tool bar, I see =A1:A8, and that’s what shows in the cell when I place the cursor in the field. But when I hit Enter, I again get #VALUE!

What am I doing wrong?

total entries in a column in calc
#7

i have the exact same issue but i’m using currency. entire column is set to currency and i get $0 as my sum.

#8

See @mikekaganski’s comment below.

#10

Yes, I tried that before posting my comment. The usability needs to be on par with the expectations of the target users, especially the most basic features like auto-sum and data format.

#12

…And the knowldge of the users needs to be on par with some basic usage of the office suites (you must know the difference of the data formats and the data types). Maybe your currency data are pure texts, but not summable numeric values.
You can check it by Value Highlighting feature, and/or please upload your sample file here.

#2

(This is not the answer!)

Okay, I see that I neglected to click the SUM symbol.

So now I click the Equal then the SUM symbol while my cursor is in the cell below the series, then click in the top cell and drag through the cells in the series.

In the tool bar field, what shows is =SUM(A1:A8)

But in the cell below, the number that comes up is ZERO (0), even though the numbers in the series most definitely do NOT equal zero.

Now, when I go back to do it again, it gives me the number that is in the bottom cell of the series, not the sum of the series, even though the formula remains the same: =SUM(A1:A8).

What gives?

#3

Your data in cells is not numbers, although looks like numbers (check with View→Value Highlighting (Ctrl+F8)). You need to convert to numbers - use Data→Text to Columns..., or see this FAQ.

#4

I see, I thought that selecting number in the formatting interface was all that I needed to do to convert cells to numbers. Thank you very much!

#9

That is certainly how it should work.

#13

And it works, if you use the right sequence and meet rules:

  • First set the type
  • Then enter data

If you set the cell to Text a german zip code 01234 will show the 0, if set to number the 0 is lost.
If yo enter a UK code Y3B Z1C in a number field it will be kept as text, becaue you can not convert to number.
.
But: Changing format of a cell never changes the value. So a '+0049 03012345678 is not converted to a number by changing format.
Format changes how a value is displayed.

1 Like
#5

Hello,

Do not use the = at all. Use the Sigma Σ symbol for Sum. The = is for formulas. Your current selection will result in =Sum(A1:A8)

Steps to Sum A1 thru A8:

  • Select cell where the total is to appear.

  • Select Sigma character from toolbar

  • Select Cell A1 & drag down to cell A8

  • Release mouse & hit enter

#6

Got it, thank you very much!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.