View original (French)

Excel: AutoSum = 0 and manual sum = error

Solved
jds2016 Posted messages 14 Status Member -  
jds2016 Posted messages 14 Status Member -
Hello,

I usually have no problem performing sums with Excel.

But now I've opened an Excel file created by someone else, and I can't perform a sum:

- either I do an automatic sum and the result =0
- or I do a manual sum (for example, I write in a cell: =A1+A2+A3) and the error message #VALUE! appears.

I want to specify that these are whole numbers and that the cell format is set to standard. After looking through various forums, I've tried changing the cell format to number, accounting, and currency, but nothing works. I've also tried converting the cells, but that doesn't change anything.

Any solution?

Thanks in advance!!!

5 answers

  1.
    Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hi,
    in your file, the SUPPRESPACE, SUBSTITUTE or REPLACE syntax doesn't work

    or with SUPPRESPACE you have to use the CAR(160) syntax
    for example in H1 you can paste one of these formulas that you increment downwards
    SUBSTITUTE(F1,CAR(160),"")*1
    or
    =LEFT(F1,LEN(F1)-1)*1

    then if you want to replace your values in column F you select your value range in column H and COPY
    you select your equivalent range in column F and
    PASTE SPECIAL/select VALUES and ok
    See you
    Mike-31

    I am responsible for what I say, not for what you understand...
    1
    1. jds2016 Posted messages 14 Status Member
       
      Hello,

      Sorry for the delayed response, I just saw your messages!

      The formula =LEFT(F1,LEN(F1)-1)*1 doesn’t work but the formula SUBSTITUTE(F1,CHAR(160),"")*1 works!

      That’s great, thank you so much!
      0
  2. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hello,

    It can be standard but certainly considered as text because there's a space in front or behind your value or even as a separator

    start by entering the value 1 in an empty cell and copy this cell, then select your range of values and right-click on your selection/paste special/check multiplication and ok
    - or the second method with a 0 and in that case paste special/addition

    if that doesn’t work, we would need to check your file
    A+
    Mike-31

    I am responsible for what I say, not for what you understand...
    0
    1. jds2016 Posted messages 14 Status Member
       
      Thank you but unfortunately I still find zero... :(
      0
  3. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Re,

    anonymize your file and post it to us
    1) Click on this link https://www.cjoint.com/
    2) Click on the Browse button to select your file
    3) Click on the Create link button at the bottom of the page
    4) After a few seconds, a link will be generated at the top of the page in blue underline, select it and copy it into a reply
    --
    A+
    Mike-31

    I am responsible for what I say, not for what you understand...
    0
  4. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Re,
    I notice that your values are on the left which confirms that your values are considered as text, so it's perfectly normal that the SUM syntax returns 0 since it's impossible to add text and #VALUE! if you're using the +

    a screenshot doesn't allow for work, I asked you for a piece of the Excel file so for my part without this file I won't go any further in the discussion
    See you+
    Mike-31

    I am responsible for what I say, not for what you understand...
    0
  6. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Re,

    it's just what I was telling you in my first post, you have a space behind each value, if you remove it your value will be positioned to the right and considered as a numeric value

    --
    A+
    Mike-31

    I am responsible for what I say, not for what you understand...
    0