Last week Vijay asked how he can figure out the sum of the digits in a value. And he wanted to do it using a non-array formula .

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

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

For the people who enjoys hitting

**01. Formula - Sum & Index**Assuming a value is entered in cell B4 i.e. 45454. The sum of the digits of this number is 22.

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

**02. Formula - Sumproduct & Indirect**

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

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

**03. Formula - Sumproduct & Indirect**

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

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

**04. Formula - Array Formula**

For the people who enjoys hitting

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

**=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