SUM for numbers with point decimals results in 0

[emw]

Hi,

In the attached file you can see that I'm trying to perform a SUM on column H. You will find the formula on H5. I'm simply trying to perform a SUM on column HS for the values in H2, H3 and H4 that should result in "3.30". A very simple operation. Somehow the result is 0. I have checked that all cells are not set as "Text" or some other strange format and tried many other options. I have also tried to perform the operation with the built-in funtion "AutoSum" and of some reason it selects the wrong cells.

This should be a very simple task. I wonder why I'm having such problems. Any help is much appreciated.

Br
Edvard

test.xlsx

[Special-K]

H2 H3 H4 are left justified
That means they are text, hence the zero result.

[Glenn Kennedy]

i don't see wha version of Excel you are using, but the numbers ARE stored as text, Do you see a small green triangle in the top left corner of the cells? Ifso,select the cells, and click on the exclamation mark in the yellow diamond to convert to numbers.

[emw]

Hi,

Thanks for the fast replies.

The version I'm using is 14.0.6023.1000 (32-bit)

I have set numbers to be justified to the right and the cells are set as "General", not as "Text". Either way, I have changed the cells to "Number" and the only visible changes, is that sum is now presented as "0,00" instead of "0".

I can't see any green triangles or exclamation marks/warning texts whatsoever. I have seen them in other files so I know how they like like, but not in this one.

To sum it up, I'm stuck as before.

Br
Edvard

[Jonmo1]

Changing the 'Format' of cells from General to Number (or anything) doesn't change the actual value in the cell.
And just becaue the cell is Formatted as a Number, doesn't mean that cell actually 'Contains' a Number.
A cell formatted as Number CAN contain a Text Value.
If you type "Hello" into a cell, and then change that cell's format to 'Number', doesn't make "Hello" change to a number.

The 'Values' in the cells are NOT real numbers.
They are called 'Numbers Stored As Text'

If you don't see the green triangle, it likely means that option is turned off in Excel Options - Formulas, "Enable background error checking"


Another method to convert them from 'Numbers stored as text' to real numbers..
Highlight the column
Data - Text to Columns
Deliminated - Next
UNcheck all options - Finish.

[emw]

Jonmo1,

Thanks for the explanation.

If you don't see the green triangle, it likely means that option is turned off in Excel Options - Formulas, "Enable background error checking"

This is already enabled.


Another method to convert them from 'Numbers stored as text' to real numbers..
Highlight the column
Data - Text to Columns
Deliminated - Next
UNcheck all options - Finish.


Tried this as well. No change.

Br
Edvard

[AlKey]

Try this formula sum values in column H

=SUMPRODUCT(--(H2:H10))

[PeteABC123]

Another way to convert to a number: Multiply H2 by 1. Copy down and do your =SUM.
Pete

[emw]

=SUMPRODUCT(--(H2:H10))

Tryhing this formula I'm getting the following Warning:

"A value used in this formula is of the wrong data type"

Just wondering which and how?

Br
Edvard

[AlKey]

=SUMPRODUCT(--(H2:H10))

Tryhing this formula I'm getting the following Warning:

"A value used in this formula is of the wrong data type"

Just wondering which and how?

Br
Edvard

Please see attached file

[emw]

I have attached a picture regarding the calculation steps and noticed that the numbers are within "". Is that really correct?

Are the content within the cells still interpreted as "Text"?

Br
Edvardexcel-error.png

[emw]

AlKey,

Downloaded your file and saw that the formula is working. But as soon as I update any number on the current cells, the formula spits out the same Warning as before. I don't get it.

Br
Edvard

[Jonmo1]

I bet your regional settings have comma as the decimal seperator.

What if you type in the values as
1,10

and try

=SUMPRODUCT(--(SUBSTITUTE(H2:H4;".";",")))
This is meant to be tested prior to changing . to ,

[emw]

Jonmo1,

There you have it! Now the formula is working correctly as soon as I change to ","

The things is that all the numbers and data that I have are using "." How do I change this setting?

Br
Edvard

[Jonmo1]

How do I change this setting?

In Windows, it's in Control Panel - Regional Settings - Additional Settings.

[emw]

Jonmo1,

Applied the change and now everything is working perfectly.

Big thanks for the help! Much appreciated.

Br
Edvard

[Jonmo1]

You're welcome.