column won't sum

[Guest]

I was trying to figure out why my phone bill tripled this month so I copied
and pasted the list of my alleged calls int an excel spreadsheet. when I
tried to sum the column with the per call charges in it it is always zero.

ie. in cell h235 I enter "=sum(h4:h234)" without quotes. The result is
always zero. It doesn't matter whether I keep the source formatting or match
destination formatting or change the formatting to number under "format
cells." None of the paste special options are useful either. Also if I set
the formula to say "=h34" it will correctly show the value from that cell.
I'm at a loss.

 

[Harlan Grove]

CK wrote...

I was trying to figure out why my phone bill tripled this month so I copied
and pasted the list of my alleged calls int an excel spreadsheet. when I
tried to sum the column with the per call charges in it it is always zero.

ie. in cell h235 I enter "=sum(h4:h234)" without quotes. The result is
always zero. It doesn't matter whether I keep the source formatting or match
destination formatting or change the formatting to number under "format
cells." None of the paste special options are useful either. Also if I set
the formula to say "=h34" it will correctly show the value from that cell.
I'm at a loss.

Excel thinks your per call charges are all text. Try the following
formula.

=SUMPRODUCT(--SUBSTITUTE(H4:H234,CHAR(160),""))

 

[CLR]

It appears that your data is still text, even tho you've tired to re-format
it..........this is a difficulty much experienced with imported
data............even when you do =H34, you're probably still getting a TEXT
value.

Try using Data > TextToColumns > delimited > use NO delimiter (uncheck
everything) and choose {NONE} as the text qualifier..........hopefully this
will convert your TEXT numbers back to number-numbers.........

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

That worked, I'm not sure what it is but it worked
Thanks

 

[Guest]

Your solution almost worked too but i needed to paste into a *.txt first and
import that and select space delimited. After that it worked like butter.