Excel : Sum of Digits in a Number using Non array Formulas


Excel Course : The Ultimate Excel Programmer Course

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
Coursera Data Science

Excel Tutorials : 100 Excel Tutorials

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

2 Responses to "Excel : Sum of Digits in a Number using Non array Formulas"

  1. There is one more.
    ctrl+shift+enter:
    =SUM(IFERROR(1*MID(B4,ROW($1:$99),1),))

    ReplyDelete

Next → ← Prev