Excel: 4 Ways to Sum Digits in a Number

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

Spread the Word!
Share Share Tweet

About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

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

3 Responses to "Excel: 4 Ways to Sum Digits in a Number"

yuvrajApril 20, 2015 at 5:38 AM
plz explain it
UnknownNovember 6, 2016 at 12:08 AM
There is one more.
ctrl+shift+enter:
=SUM(IFERROR(1*MID(B4,ROW($1:$99),1),))
AnonymousFebruary 22, 2019 at 2:04 AM
THE FORMULAS WONT WORK FOR CELL WITH LINE BREAKS NUMBER VALUE