Sum returns 0 and seems not to recognice the value.

[jose cedeño]

I'm trying to sum some values on a worksheet, I downloaded the info from internet and it would allow me to change the info but whe I do the automated sum it woul give either 0 or an error and also I just noticed that it's not recognicing any of the values as numbers. I uploaded 2 pics to show what I'm trying to explain.shows column D with valuesShows the error and blank spaces as 0

[#2]

Hi Jose

Thanks for the screen shots, it helps to see the problem!

From your first image, it appears that your Debito column is Text, therefore the SUM formula will not work.

Can I suggest the following:

1. Select columns D and E.
2. In the Home tab under the Number section, drop down the list and select Currency.
3. Both columns should now reflect $ currency values and your SUM formula (e.g. =SUM(D2:D13)) should work now.

See if this works for you?

I've also attached a sample file for you to see the formula and format in action, so you can have a play with it if you want.

Please let us know how you go.

Cheers

Damien

[#3]

Hi Damien, it seems to have something to do with the source I guess because I've tried everything already and wouldn't work. do you know how to change the confidence options, that's the only thing I haven't done yet.

[#4]

It looks like unprintable characters are columns since the source is copy/pasted from internet, e.g.

228.20&CHAR(10) in D3.

If so array formula

=SUM(IFERROR(VALUE(CLEAN(D6:D93)),0))

could help

[#5]

Hello, at least now with the clean function it shows numbers but still giving me back error and since the formula state if error is 0 turns everything in 0.here is the formula is in spanish but I'm sure the functions are correctshowing the valuesshowing the error

[#6]

Hi Jose,

What is the decimal separator in your locale, dot or comma? Based on link below it shall be comma for Spanish locale, but your numbers are with dots.

https://docs.oracle.com/cd/E19455-01/806-0169/overview-9/index.html