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