In this article, we explore various methods to calculate the sum of digits in Excel. The inspiration for this article came from a question by a colleague who asked about this specific calculation.

Assuming a value is entered in cell **B4** i.e. **45454**. The sum of the digits of this number is **22**.

## 1. SUM & INDEX Formula

=SUM(INDEX(1*(MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1)),,))

Here is a breakdown of the formula:

`MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1)`

extracts each digit from the cell B4. It uses the MID function to take one character at a time, starting from the first character (1), up to the length of the number (LEN(B4)).`1*(MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1))`

converts the extracted digits from text to numeric values by multiplying them by 1.`INDEX(...)`

is used to create an array of the individual digits.`SUM(...)`

calculates the sum of all the digits in the array.

## 2. SUMPRODUCT & INDIRECT Formula

=SUMPRODUCT(MID(B4,ROW(INDIRECT("1:" & LEN(B4))),1)*1)

Instead of using SUM and INDEX, we are using the SUMPRODUCT(...) which multiplies each digit by 1 and then sums up all the products, resulting in the sum of the digits.

## 3. SUMPRODUCT & OFFSET Formula

=SUMPRODUCT(MID(B4,ROW(OFFSET($A$1,,,LEN(B4))),1)+0)

The ROW and OFFSET functions are used to generate a dynamic array of row numbers corresponding to the length of the number. +0 is added to convert the extracted digits from text to numeric values.

## 4. Array Formula

Those who don't mind hitting **Ctrl Shift Enter** instead of regular ENTER button. The array formula is generally considered complex and difficult to handle and maintain.

=SUM(MID(B4,ROW(INDIRECT("1:" & LEN(B4))),1)*1)Note : Press F2 and Hit

**Ctrl Shift Enter**to confirm the above array formula.

**Download the Workbook**

plz explain it

ReplyDeleteThere is one more.

ReplyDeletectrl+shift+enter:

=SUM(IFERROR(1*MID(B4,ROW($1:$99),1),))

THE FORMULAS WONT WORK FOR CELL WITH LINE BREAKS NUMBER VALUE

ReplyDelete