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

ReplyDeleteVery efficiently written information. It will be beneficial to anybody who utilizes it, including me. Keep up the good work. For sure i will check out more posts. This site seems to get a good amount of visitors. Click here

ReplyDeleteVery efficiently written information. It will be beneficial to anybody who utilizes it, including me. Keep up the good work. For sure i will check out more posts. This site seems to get a good amount of visitors. Click here

ReplyDelete