Welcome Guest
You last visited December 13, 2017, 1:26 pm
All times shown are
Eastern Time (GMT-5:00)

Excel question - I'm stuck

Topic closed. 6 replies. Last post 9 years ago by Bandito.

 Page 1 of 1
New Member

United States
Member #62904
July 12, 2008
5 Posts
Offline
 Posted: July 12, 2008, 9:55 pm - IP Logged

Hey everyone,

Newbie here to the forum.. I am stuck on a Excel problem.   I have read the posts and see that Rakster has some similar questions on this topic, I tried the suggestions he was offered.. they work to a degree but not to the level I need.. here is my problem:

I would like to add the digits of a two or three digit number within a cell... for example:

a two digit number say 94 in A1..  those two digits added together, 9+4=13.   I would like to further add

13, 1+3=4..   Same for a three or more digit number...  185 would be 1+8+5=14,  taking it further, 1+4=5.

Can someone come up with a single formula which can do this?   Many thanks in advance for your help..

Pennsylvania
United States
Member #2218
September 1, 2003
5641 Posts
Offline
 Posted: July 12, 2008, 11:40 pm - IP Logged

Hey everyone,

Newbie here to the forum.. I am stuck on a Excel problem.   I have read the posts and see that Rakster has some similar questions on this topic, I tried the suggestions he was offered.. they work to a degree but not to the level I need.. here is my problem:

I would like to add the digits of a two or three digit number within a cell... for example:

a two digit number say 94 in A1..  those two digits added together, 9+4=13.   I would like to further add

13, 1+3=4..   Same for a three or more digit number...  185 would be 1+8+5=14,  taking it further, 1+4=5.

Can someone come up with a single formula which can do this?   Many thanks in advance for your help..

Bandito,

Try this:

Open Excel

• Press Alt+F11 to open Visual Basic Editor (VBE).
• Copy and Paste the following lines of code into the module (Blank box on the right side):

Function SumDigits(Number)
Dim i As Integer
For i = 1 To Len(Number)
SumDigits = SumDigits + Val(Mid(Number, i, 1))
Next i
End Function

Close the Visual Basic Editor.

To test the function:

• To test the function, open Paste Function (Shift+F3).
• From the User Defined category (use the drop-down arrow), select the "SumDigits" function.
• In the function box, select any cell that contains a number.
• Click OK.
• Good for Pick 3 and Pick 4 digit numbers or any size number

So whatever number is in the cell this new formula will add the number in the cell you are formulating.

New Member

United States
Member #62904
July 12, 2008
5 Posts
Offline
 Posted: July 12, 2008, 11:53 pm - IP Logged

Thanks winsumloosesum, that works but it dosen't do exactly what I'm looking for..

for example, I enter the VB formula (Sumdigits) into my spreadsheet and it adds the digits

in the cell.   Say I have 95 in A1.. the formula does return 14.  But I'm looking for a formula which would

return 5...  9+5=14, then adding 1+4 to get 5.  Looking for a single formula which would do this???

Thanks,

Bandito.

New Member
tacoma
United States
Member #35425
March 17, 2006
1 Posts
Offline
 Posted: July 13, 2008, 1:36 am - IP Logged

click on cell you want total value in and enter this command =sum(9,5) for every additional number in cell just add a comma between them.

New Mexico
United States
Member #58526
February 18, 2008
683 Posts
Offline
 Posted: July 13, 2008, 1:54 am - IP Logged

Just use any of the above suggestions to create a total for cell A1 in cell B1, then use it again to total cell B1 and put that in cell C1 (or whatever format works for you)  If you don't want to see the intermediate values you can hide those columns.

United States
Member #13130
March 30, 2005
2171 Posts
Offline
 Posted: July 13, 2008, 4:55 am - IP Logged

...slight variation of WSLS's work (this is good unless you're adding the digits of excessively large numbers...)

Function SumDigits(Number)
Dim i As Integer, fake__sum As Integer

While Number > 9
fake__sum = 0
For i = 1 To Len(Number)
fake__sum = fake__sum + Val(Mid(Number, i, 1))
Next i
Number = fake__sum
Wend
SumDigits = fake__sum
End Function

to use, type in =SumDigits(number) in any cell, or call from other precedures.

In neo-conned Amerika, bank robs you.
Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.