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 .

**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**
### About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.

*While I love having friends who agree, I only learn from those who don't. *
Let's Get Connected: Email | LinkedIn

### Related Posts:

plz explain it

ReplyDeleteThere is one more.

ReplyDeletectrl+shift+enter:

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