# csv column weird

I imported a csv delimitered file to a spreadsheet and there was a column of $values....$400.00...$350.00 etc.

On the above a cells formula of ...= (A1+A2) will produce 750.00 (no $)

But if I try SUM or COUNT etc on a column of such derived numerical-only cells, it fails. They all produce zero ....0.

If I do COUNTA, it will give me a figure (surely a COUNT is a COUNT of cells?)

But sum(A5:A12) = 0

I tried formatting the column to a simple numerical format, but it stayed unchanged...formatted as currency (???), and still no ability to SUM or COUNT, but still with the ability to have MATH operators on individual cells.

If I take a cell and ask for "=a5", I get A5's value...but if I try a Sum on a column of such results......0.

However if I enter the formula "= A5*2" I get a value of twice A5. So I enter "=A5*2/2" and it gives me a value of the value of A5.

....and then if I copy and paste that formula to a whole column, I can SUM(G5:G12) and it works...as does COUNT.

I am puzzled and would greatly appreciate help, please. All I wanted was to sum a column of imported, apparently numerical, figures. Even now I have a workaround it will be a nuisance.

Nick

EDIT: Further to this, if I actually enter -400.00 into a range of cells that are in my imported column, then format those cells as Numeric 1000.00, I _still_ cannot SUM them. I noticed that at the beginning of each cell's text there was a single quote. Removing that manually from each cell made SUM work.

Asking for such ideas a RIGHT (cell number) X digits still did not let SUM etc work.

Your Edit comment suggests the cells are formatted as Text. You can confirm this with View | Value Highlighting (Ctrl+F8). Text cells are formatted in black, formulae in green, and number cells in blue, no matter how their display is formatted.

OK. Thanks yes you are right. They are text.

But I am confused: - if I Format Cells and ask for them to be numeric, why does that not work? - if they are text, how come I can do such maths as A2+A3 and get a numeric result?

Nick

Changing the format of a cell doesn't change the contents; if the content is text, it will remain text no matter how you format the cell.

So my question remains. If it is still text, then how come maths on that cell will work (A3 = 3, as in 2X A3 = 6 in another c) but SUM and COUNT will not. How come if I create a columnF that is based on F=A2*2/2, then copy and paste that formula to F3:F20, the column becomes numeric and can be SUMmed and COUNTed, because I did a MATH calc on a TEXT cell?

Not just being combative. It seems like the TEXT cell is totally different from the column it's in. Maths on cell = OK. Stats on Column = not