If we enter a number 1234 in a single cell and we want to get the sum of all digits like 1+2+3+4=10 in another cell, how can we do? As there is no function like SUM to sum all digits in a cell, we need to use a formula with some functions together to do sum.

**Precondition:**

Can you sum all digits in a number quickly?

Table of Contents

**Method 1: Sum All Digits in A Cell by Formula**

**Step 1: **In B1 enter the formula **=SUMPRODUCT(1*MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1))**. Then click **ENTER**.

**Step 2:** Drag down the fill handle till reaching the last cell in the table.

**Method 2: Sum All Digits in A Cell by User Defined Function Via VBA Code**

This formula is so long and contains many functions, if we can create a function to sum all digits, it will be much better. Actually, we can via VBA code to define a user defined function.

**Step 1:** On current visible worksheet, **right click** on sheet name tab to load Sheet management menu. Select **View Code**, **Microsoft Visual Basic for Applications** window pops up.

Or you can enter **Microsoft Visual Basic for Applications** window via **Developer->Visual Basic**. You can also press **Alt + F11** keys simultaneously to open it.

**Step 2:** In **Microsoft Visual Basic for Applications** window, click **Insert->Module**, enter below code in Module1:

Function SUMALLDIGITS(Num As String) As Integer Dim Sum As String Dim i As Integer For i = 1 To Len(Num) Sum = Mid(Num, i, 1) If IsNumeric(Sum) Then SUMALLDIGITS = SUMALLDIGITS + Sum Next End Function

**Step 3:** Save code and then quit **Microsoft Visual Basic for Applications**. Now **SUMALLDIGITS** function is created.

**Step 4:** In B2 enter **=sum** to load all functions contain SUM. Verify that **SUMALLDIGITS** is displayed.

**Step 5:** Select **SUMALLDIGITS **function, select A1, then click **Enter**. Verify that all digits’ total is calculated correctly.