View original (French)

Sum of digits in the same Excel cell

Solved
jubalive Posted messages 12 Status Member -  
JvDo Posted messages 1924 Registration date   Status Member Last intervention   -
Hello, here is the principle of my problem
I need to count the number of boxes A
and for that I created a file, in A1 I put "boiteA" and in A2 I must enter different numbers 4,2,8,7,... always in the same cell A2
and I want to have the result of all the numbers I entered in cell A2 in cell A3
that is: in A2 I enter 3 then 2
I must have in A3 the value 5
then I enter again in A2 the number 4
I must have in A3 the value 9

I hope you understand me...thanks
Configuration: Windows XP Firefox 3.0.1

9 answers

  1.
    JvDo Posted messages 1924 Registration date   Status Member Last intervention   859
     
    Hello,
    use the formula :
    =SUM(1*STXT(A2;ROW(INDIRECT("1:"&ENT(LOG(A2))+1));1))
    and validate it with CTRL+SHIFT+ENTER
    sincerely
    7
    1. MadinLady Posted messages 1 Status Member
       
      Good evening jvdo, thank you for your formula which works really well. I have another challenge: I would like to reduce the result to a single digit, so if the formula gives a result > 10 I want to reduce it to a single digit without using an if statement that would make the formula too long, and without using another cell for an intermediate calculation. For example, adding 1996 gives 25, but I want 7 as the final result. Thanks for your reply.
      0
    2. JvDo Posted messages 1924 Registration date   Status Member Last intervention   859
       
      Hello,

      you add a modulo 9.

      best regards
      0
  2. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    Hello, jubalive.

    Excel (nor any spreadsheet in the world) isn’t designed to do this kind of thing.
    What’s stopping you from working normally, that is, entering your values into separate cells and summing them in a new cell?
    The space? You’ll always have enough of it.
    The presentation? You can always hide rows or columns.
    What else?

    Besides, even the formula of JvDo, though extremely sophisticated (I didn’t even understand how it could work), can’t solve your problem...
    Every time you enter a new value in A2, it takes the place of the previous one!
    Maybe there’s only one macro that could provide an answer to your request. So let’s hope a VBA specialist will take an interest...
    --
    C’est bien, la retraite ! Surtout aux Antilles... :-)
    ☻ Raymond ♂
    0
  3. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello,

    Not only does the formula proposed by Jvdo work very well, but on top of that, it is superb and of a very high standard: congratulations to Jvdo: come back and visit us more often!
    Its usefulness (I’m talking about the formula!), I don’t know, so I’ll say nothing...
    --
    Best regards, Michel
    0
  4. JvDo Posted messages 1924 Registration date   Status Member Last intervention   859
     
    Hello everyone,
    Jeanpierre, on XLD, proposed a clever solution (https://www.excel-downloads.com/threads/somme-des-chiffres-entres-dans-une-meme-cellule.126973/) based on circular references and iterations limited to 1.
    For the formula, I didn’t invent much. It’s frequently found in responses on CCM or XLD (J. Boisgontier, L. Longre, Ti, .....).
    A step-by-step shows how it works.
    The slightly strange trick is that ENT(LOG(A2))+1 can be replaced by NBCAR(TEXTE(A2;"#")).
    Best regards and see you soon
    0
    1. Yaniboop
       
      Hello JvDo,
      I’d like to know if it’s possible to have it computed not with just a single number but, for example, by adding 1234561011121314151718(19) the digits by tens???
      0
    2. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
       
      Good evening,

      It would have been better to start your own discussion and not to resurrect this very old one.
      you have these values in a cell 1234561011121314151718 what does this value (19) correspond to
      what is the expected result based on this string
      0
  6. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Sorry, but like many others, I have permanently disconnected from this site XLD after having learned a lot there and contributed a bit since 2002-2003
    --
    Michel
    0
  7. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    JvDo, michel_m, hello to you.

    I tried the JvDo formula again, it still doesn’t work for me!
    What did I miss? I’ve indeed entered {=SUM(1*STXT(A2;ROW(INDIRECT("1: "&LOG(A2)+1));1))} in my formula bar ...
    Everything happens as if I had entered =A2! with every new entry in A2, I get this new value, with no accumulation.

    Thanks in advance.
    --
    C'est bien, la retraite! Surtout aux Antilles ... :-)
    ☻ Raymond ♂
    0
  8. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hi,

    This matrix is used to count every digit in a cell, provided there are no gaps or generic signs between each digit. Example: in A2 you enter 123456789 and the formula returns the total, which is 45.

    =SOMME(1*STXT(A2;LIGNE(INDIRECT("1:&ENT(LOG(A2))+1"));1))

    If there are generic signs between digits, you’ll need a conversion or modify the formula, which will be long for 9 entries, and similarly for tens; for example 12 and 12 the formula returns 6 instead of 24.

    Best regards,
    Mike-31

    An unsolved problem is a poorly posed problem (Einstein)
    0
  9. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    Raymond
    I don’t know how it is with you, but where I am it works and it’s particularly impressive!
    Bravo!

    --
    Let us ask ourselves if we are not alone in understanding what we explain?
    0
  10. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    Thank you, Mike, I understand better! I had stuck to the initial request from jubalive: "A2 I enter 3 then 2 I should have in A3 the value 5 then I enter again in A2 the number 4 I should have in A3 the value 9" Also I expected that at each entry we would press Enter and read the new total... Thanks again, Mike, and best wishes to Vaucluse. -- It's really retirement! Especially in the Antilles... :-) Raymon d
    0