Adding ALL Digits in a Range

[Paul]

Hi Everyone,

I have the Following Formula ( Posted by Bernd ) that Adds the Digits in
a Single Cell Together to give a Total. For Example if Cell "A1" had
123456789 the Formula would Return the Answer 45.
Is there Anyway to Adapt the Following Formula ( which is Array Entered
) :-

=SUM(VALUE(MID(A10,ROW(A1:OFFSET(A1,LEN(A10)-1,0)),1)))

So if I had the Following Data in Cells :-

A10 = 9
B10 = 13
C10 = 20
D10 = 32
E10 = 41
F10 = 46

It Adds the Digits Together 9+1+3+2+0+3+2+4+1+4+6 and gives the Total
35.
I know that I Could Concatenate A10:F10 and then Apply the Formula, But
it would be Nice if it Could be Done Within One Formula.
It is Basically the Sum of ALL Digits ( NOT Cell Values ) from A10:F10.
Is there Also a Macro Available that will do this Please.

Thanks in Advance.
All the Best
Paul

 

[Norman Jones]

Hi Paul,

Is there Also a Macro Available that will do this Please

Try:

Function SumOfDigits(Rng As Range)
Dim cell As Range
Dim i As Long
Dim mySum As Long
Dim sStr As String

For Each cell In Rng
If IsNumeric(cell.Text) Then
For i = 1 To Len(cell.Text)
sStr = Mid(cell.Text, i, 1)
mySum = mySum + CLng(sStr)
Next i
End If
Next cell

SumOfDigits = mySum

End Function

Using your example, worksheet entry would be:

=SumOfDigits(A10:F10)

or, if called from another workbook:

=AnorherWorkBookName!SumOfDigits(A10:F10)

In VBA, use like:

Sub GetSum()
Dim myVar As Long
myVar = SumOfDigits(Sheets("Sheet1").Range("A10:A11"))
MsgBox myVar
End Sub

 

[Ron Rosenfeld]

Hi Everyone,

I have the Following Formula ( Posted by Bernd ) that Adds the Digits in
a Single Cell Together to give a Total. For Example if Cell "A1" had
123456789 the Formula would Return the Answer 45.
Is there Anyway to Adapt the Following Formula ( which is Array Entered
) :-

=SUM(VALUE(MID(A10,ROW(A1:OFFSET(A1,LEN(A10)-1,0)),1)))

So if I had the Following Data in Cells :-

A10 = 9
B10 = 13
C10 = 20
D10 = 32
E10 = 41
F10 = 46

It Adds the Digits Together 9+1+3+2+0+3+2+4+1+4+6 and gives the Total
35.
I know that I Could Concatenate A10:F10 and then Apply the Formula, But
it would be Nice if it Could be Done Within One Formula.
It is Basically the Sum of ALL Digits ( NOT Cell Values ) from A10:F10.
Is there Also a Macro Available that will do this Please.

Thanks in Advance.
All the Best
Paul

Here is a simple UDF that will do that. To enter this, <alt-F11> opens the VB
Editor. Ensure your project is highlighted in the project explorer window,
then Insert/Module and paste the code below into the window that opens.

To use this, entire the function =SumDigits(rg) where, in your example, replace
rg with A1:F10.

This function will add all of the digits in the cells. So, for example, if a
cell contains !b12.34CD it will show 1+2+3+4 or 10.

=============================
Function SumDigits(rg As Range) As Long
Dim c As Range
Dim digit As Integer
Dim i As Integer

For Each c In rg
For i = 1 To Len(c.Text)
SumDigits = SumDigits + Val(Mid(c.Text, i, 1))
Next i
Next c

End Function
======================


--ron

 

[Bob Phillips]

=SUMPRODUCT(--((MID(CONCATENATE(A10,B10,C10,D10,E10,F10),ROW(INDIRECT("1:"&L
EN(CONCATENATE(A10,B10,C10,D10,E10,F10)))),1))))

--

HTH

RP
(remove nothere from the email address if mailing direct)