Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding ALL Digits in a Range
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding ALL Digits in a Range
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 --- Regards, Norman "Paul" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding ALL Digits in a Range
On Thu, 02 Dec 2004 03:48:40 -0800, Paul
wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding ALL Digits in a Range
=SUMPRODUCT(--((MID(CONCATENATE(A10,B10,C10,D10,E10,F10),ROW(IND IRECT("1:"&L
EN(CONCATENATE(A10,B10,C10,D10,E10,F10)))),1)))) -- HTH RP (remove nothere from the email address if mailing direct) "Paul" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding digits with moving decimals | Excel Worksheet Functions | |||
Adding digits | Excel Worksheet Functions | |||
adding extra digits to columns | Excel Discussion (Misc queries) | |||
adding digits of a number | Excel Worksheet Functions | |||
adding digits to front/end of fax numbers | Excel Discussion (Misc queries) |