Any Excel expert here?

I am not too sure but maybe trying to build a macro for it?

 

http://excel.tips.net/Pages/T002424_Summing_Digits_in_a_Value.html

That tells you how to do it for an individual cell, but not for a range. Any help?

 

Nice tip,

Presumably he would need to create a third column which sums the individual cells, and then sum that column

 

Can you not do a SUM?

 

That's what I would do. You could even hide the column with the data that you don't want to see.

 

Yeah, after the link I posted, it would be easy to sum the results in another cell, but it seemed like he wanted to do it all in one cell, which I'm not sure about. I use Excel a lot, but usually not with a lot of crazy formulas.

 

I got curious about this and wrote up a quick macro to calculate it and put it in a target cell. Just fill in the first 6 values to suit your range.

Code:

Sub CalculateDigits()
    Dim ls_Worksheet As String
    Dim ls_TargetRangeStartColumn As String
    Dim ls_TargetRangeEndColumn As String
    Dim ls_TargetRangeStartRow As String
    Dim ls_TargetRangeEndRow As String
    Dim ls_DisplayResultRange As String
    
    Dim li_DigitIndex As Integer
    Dim li_ColumnIndex As Integer
    Dim li_ColumnIndexStart As Integer
    Dim li_ColumnIndexEnd As Integer
    Dim li_RowIndex As Integer
    Dim li_RowIndexStart As Integer
    Dim li_RowIndexEnd As Integer
    Dim li_RowCount As Integer
    Dim li_ColumnCount As Integer
    
    Dim li_Length As Integer
    Dim ll_Result As Long
    Dim li_Row As Integer
    Dim li_Column As Integer
    Dim ls_CellValue As String
    
    '***********************************************
    '***    Set up Values here
    '***    These values calculate sum of digits
    '***    for cells C3:T100 on Sheet1
    '***
    '***    The Result is put in Cell AA1
    '***********************************************
    
    ls_Worksheet = "Sheet1" 'Paste Name of Worksheet here
    ls_TargetRangeStartColumn = "C"
    ls_TargetRangeStartRow = "3"
    ls_TargetRangeEndColumn = "T"
    ls_TargetRangeEndRow = "100"
    ls_DisplayResultRange = "AA1"
    ls_TargetRangeStartColumn = UCase(ls_TargetRangeStartColumn)
    ls_TargetRangeEndColumn = UCase(ls_TargetRangeEndColumn)
    
    If (Len(ls_TargetRangeStartColumn) = 1) Then
        li_ColumnIndexStart = Asc(ls_TargetRangeStartColumn) - 64
    Else
        li_ColumnIndexStart = Asc(Mid(ls_TargetRangeStartColumn, 2, 1)) - 64
        li_ColumnIndexStart = li_ColumnIndexStart + (Asc(Mid(ls_TargetRangeStartColumn, 1, 1)) - 64) * 26
    End If
    
    If (Len(ls_TargetRangeEndColumn) = 1) Then
        li_ColumnIndexEnd = Asc(ls_TargetRangeEndColumn) - 64
    Else
        li_ColumnIndexEnd = Asc(Mid(ls_TargetRangeEndColumn, 2, 1)) - 64
        li_ColumnIndexEnd = li_ColumnIndexEnd + (Asc(Mid(ls_TargetRangeEndColumn, 1, 1)) - 64) * 26
    End If
    
    li_ColumnCount = li_ColumnIndexEnd - li_ColumnIndexStart + 1
    
    li_RowIndexStart = CInt(ls_TargetRangeStartRow)
    li_RowIndexEnd = CInt(ls_TargetRangeEndRow)
    li_RowCount = li_RowIndexEnd - li_RowIndexStart + 1
    
    ll_Result = 0
    li_ColumnIndex = 1
    li_Column = 1
    
    Do While (li_ColumnIndex <= li_ColumnCount)
        li_RowIndex = 1
        Do While (li_RowIndex <= li_RowCount)
            ls_CellValue = CStr(Sheets(ls_Worksheet).Cells(li_RowIndex + li_RowIndexStart - 1, li_ColumnIndex + li_ColumnIndexStart - 1).Value)
            li_Length = Len(ls_CellValue)
            li_DigitIndex = 1
            Do While (li_DigitIndex <= li_Length)
                ls_Temp = Mid(ls_CellValue, li_DigitIndex, 1)
                ll_Result = ll_Result + CInt(Mid(ls_CellValue, li_DigitIndex, 1))
                li_DigitIndex = li_DigitIndex + 1
            Loop
            li_RowIndex = li_RowIndex + 1
        Loop
        li_ColumnIndex = li_ColumnIndex + 1
    Loop
    
    Sheets(ls_Worksheet).Range(ls_DisplayResultRange).Value = ll_Result
End Sub

 

I should have noted that the macro above will handle numbers of any length (1 digit or 20+ digits). Hopefully that helps.

 

Is there something better than EXCEL now?

I've always loathed a lot of things on there.

Mainly on how to make it presentable, more than the math.

 

I just did a VBA crash course...but nothing like this

Did you record macros and then adjusted it or did you do it from scratch?

 

But there is always a simpler way ....

The formula below will work for numbers up to six digits. If you need more digits just add more quotient functions to the top accordingly. B5 of course is the cell you're trying to add up. And it will fill nicely too, so you can put these formulas in a column and then sum them up.

=QUOTIENT(MOD(B5,1000000),100000)
+QUOTIENT(MOD(B5,100000),10000)
+QUOTIENT(MOD(B5,10000),1000)
+QUOTIENT(MOD(B5,1000),100)
+QUOTIENT(MOD(B5,100),10)
+MOD(B5,10)

 

Follow-up -- this problem is clearly an integer division problem (which is not readily apparent if you're used to VB programming), hence the solution above.

 

From scratch. I've written a lot of macros. It's just a triple nested loop with a bit of extra code to figure out how to parse the column letters into numbers.

FFB, Excel is the bomb. If you know how to use it, you can make it look good and accomplish just about anything. I use it for a pretty wide variety of things, including creating custom Word files, generating custom email newsletters, and database applications.

 

Another reason why crash courses suck! There's nothing crazy in what he did (nice job, btw!). The only thing you'd maybe have to look up would be how to access a specific cell/worksheet. Get your money back!

 

Another option you have is entering the following as an array formula (you have to hold down the Ctrl & Shift keys when you press enter)

=SUM(IF(LEN(A1:H1)>1,LEFT(A1:H1)+RIGHT(A1:H1),A1:H1))

You will know that you have done it right if you see brackets at the beginning and end of the formula after it has been entered:

{=SUM(IF(LEN(A1:H1)>1,LEFT(A1:H1)+RIGHT(A1:H1),A1:H1))}

 

The formula I posted will work up to 2 digits.

 

Haha it was free training from work, so no harm done, plus I like free lunches.

I was referring to the time he spent writing that macro. But to a pro, it will probably only take 30 minutes?