Trying to sum column but getting 0 as result

T

telewats

I've copied information into an excel spreadsheet, and I'm trying to su
certain columns, however my formula is returning the result of "0". Th
column I'm trying to total has numbers such as 2.0, 16.0, etc. so I'
thinking it has something to do with the formatting in these cells
however I can't figure out what to do to make it work. Can someon
please help
 
N

Niek Otten

The cells are probably text, although they look like numbers and you
formatted them as numbers after pasting them.
Format an empty cell as Number. Enter the number 1. Edit>Copy. Select your
"numbers". Edit>Paste special, check Multiply
 
D

David Billigmeier

It probably is due to the formatting, they are probably all formatted as
text. To fix:

Copy and empty cell.
Select your range and do a "Paste Special" -> "Add" to convert them to
numbers.

Does that help?
 
T

telewats

I've tried both suggestions posted, however neither is working. After
trying the suggestions, I checked the formatting for the cells in
question, and they show to be number formatted, however I still get 0
when I sum them.
 
N

Niek Otten

The formatting doesn't matter. Have you tried the ISNUMBER() function? I'm
pretty sure that's the problem.
Tools>Options>Calculation, Automatic checked?
 
T

telewats

The ISNUMBER() didn't work, and I've double checked the comma; all is
okay there. Just a note, I've imported this data from a online
cellphone bill, and I'm trying to total minutes by phone number. Could
there be a problem because of the format of the other doc?
 
N

Niek Otten

What do you mean "didn't work"? Did it say FALSE?

Check with the LEN function for the correct length of the number (or
actually the text, I'm sure); there may be non-printable characters in the
cell. Check the CLEAN() and TRIM() functions.
 
T

telewats

The extra spaces is definitely the problem. I've checked LEN, and I
actually have 6-7 spaces in these fields instead of the 3-4 of actual
data. I guess I'm just not proficient enough with Excel to fix this,
though, because I can't get rid of the extra spaces. I've tried CLEAN
and TRIM, but neither is removing the spaces...?
 
T

telewats

I was finally able to correct this issue, and I just thought I'd let
everyone know how in case you ever run into this yourself (of course,
your probably smarter than I am and already know how to do this!)

I use =CODE(RIGHT(XX,1)) to determine the numeric code for the last
character in my string. This brought back char(160), which =TRIM and
=CLEAN won't eliminate.

I then highlighted one of the cells, pressed F2 then SHIFT+Left Arrow
to highlight the spaces I needed to remove. I copied these to the
clipboard.

I then highlighted all the cells I needed changed, and used
EDIT/REPLACE, and pasted (CTRL-V) the copied info and left "Replace
With" blank. I chose Replace All.

I am now able to sum all the fields as needed!

Thanks to everyone that posted info for me --- I couldn't have found
this result if it hadn't been for you.
 
Top