Summation not including single digit and double digit numbers

[haganator]

Greetings everyone,

I have 2 spreadsheets that I'm using...one is to cut data from and the other is to paste data into. I am transferring 2 columns of numbers with each column having about 200 rows, between the spreadsheets. I am only trying to sum these 2 columns together, but for some reason, not all of the numbers are "seen" by the summation. I first noticed the problem when my sum of 2 numbers was 0. I typed over the numbers and then the sum showed the correct amount. As I continued my "investigation", it became apparent that only single digit and 2 digit numbers were ignored by the sum, unless I manually typed over them. Triple digits were summed correctly. I have tried changing the format to numbers with 0 decimal places, but that didn't work. I don't have any rules or conditional formatting, but I can't seem to fix this one. Manually typing the numbers works, but that's a lot of time.

Any ideas would be greatly appreciated.

Thanks,
Jeff

 

[Marcílio_Lobão]

Take a look at the cells.
Some of them may be acting as TEXT.

TEXT is LEFT aligned by default.
NUMBER is RIGHT aligned by default.

 

[haganator]

Take a look at the cells.
Some of them may be acting as TEXT.

TEXT is LEFT aligned by default.
NUMBER is RIGHT aligned by default.

I have changed the formatting to numbers with 0 decimal places, but they still don't show up in the sum.

 

[Marcílio_Lobão]

When you have data typed, changing format don´t take effect as desired.

That´s the reason when you manually type the numbers it works.
Is a new content with a pre-defined format.

Try to use an ARRAY formula: =SUM(VALUE(A1:A100)) then type CTRL+SHIFT+ENTER

When you do this, at FORMULA BAR, it appears {=SUM(VALUE(A1:A100))}

I hope it helps.

 

[haganator]

Marcílio,

I was able to find the problem before your post. Thank you for helping and spending some time on this. The issue had something to do with the way I was extracting information from my database. For single and double digit numbers, the script was adding tabs, multiple tabs, and/or spaces in front of the numbers. I had to edit my .csv with a notepad and strip off all the extra characters.

Thanks,
Jeff