Excel Function Question
Posted by Gay Myrick on September 24, 2001 12:51 PM
My boss has asked me to come up with a formula or
function to add numbers together in a single cell
and show the result in the next cell. (ie. Cell A1
contains the numbers 28025. Cell B1 should contain the
result, which is 17.) Can anyone help?
Gay Myrick
Posted by Aladin Akyurek on September 24, 2001 1:14 PM
Gay,
Supposing the input is in A1,
in B1 enter: =SUMPRODUCT((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))+0)
Aladin
===========
Posted by Mark W. on September 24, 2001 1:16 PM
{=SUM(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0)}
where A1 contains your value.
Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.
Posted by Juan Pablo on September 24, 2001 1:18 PM
This is a UDF, to use it you have to go to Tools - Macro - Visual Basic Editor. Once there goto Insert - Module and copy - paste this:
Function SumDigits(Rang As Excel.Range) As Long
Dim i As Integer, S As Long
If Rang.Count = 1 And IsNumeric(Rang) Then
S = 0
For i = 1 To Len(Rang)
S = S + CLng(Mid(Rang, i, 1))
Next i
SumDigits = S
End If
End Function
Now, go back to excel, and use it as a regular formula...
In A1 put 28025
In A2 this formula =SumDigits(A1)
and it should result in 17.
Juan Pablo
----------------------
Posted by Gay Myrick on September 25, 2001 5:08 AM
THANK YOU all 3 who replied to this question.
It works and my boss is a happy camper! I must
admit, I would have NEVER figured this one out
by myself. God Bless!
Gay
