In this article, we explore various methods to calculate the sum of digits in Excel. The inspiration for this article came from a question by a colleague who asked about this specific calculation.
Assuming a value is entered in cell B4 i.e. 45454. The sum of the digits of this number is 22.
1. SUM & INDEX Formula
=SUM(INDEX(1*(MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1)),,))
Here is a breakdown of the formula:
MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1)
extracts each digit from the cell B4. It uses the MID function to take one character at a time, starting from the first character (1), up to the length of the number (LEN(B4)).1*(MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1))
converts the extracted digits from text to numeric values by multiplying them by 1.INDEX(...)
is used to create an array of the individual digits.SUM(...)
calculates the sum of all the digits in the array.
2. SUMPRODUCT & INDIRECT Formula
=SUMPRODUCT(MID(B4,ROW(INDIRECT("1:" & LEN(B4))),1)*1)
Instead of using SUM and INDEX, we are using the SUMPRODUCT(...) which multiplies each digit by 1 and then sums up all the products, resulting in the sum of the digits.
3. SUMPRODUCT & OFFSET Formula
=SUMPRODUCT(MID(B4,ROW(OFFSET($A$1,,,LEN(B4))),1)+0)
The ROW and OFFSET functions are used to generate a dynamic array of row numbers corresponding to the length of the number. +0 is added to convert the extracted digits from text to numeric values.
4. Array Formula
Those who don't mind hitting Ctrl Shift Enter instead of regular ENTER button. The array formula is generally considered complex and difficult to handle and maintain.
=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
ReplyDeletewondering how I can utilize this function with decimals
ReplyDelete