Article ID: 214053 - Last Review: September 27, 2003 - Revision: 3.1

XL2000: Formula to Sum Digits of a Number

View products that this article applies to.

This article was previously published under Q214053

SUMMARY

This article describes formulas that you can use to find the sum of the digits of a number in Microsoft Excel 2000.

Back to the top

MORE INFORMATION

Formula 1: Sum the Digits of a Positive Number

To return the sum of the digits of a positive number contained in cell A10, follow these steps:

Start Excel 2000.
Type 123456 in cell A10.
Type the following formula in cell B10:
=SUM(VALUE(MID(A10,ROW(A1:OFFSET(A1,LEN(A10)-1,0)),1)))
Press CTRL+SHIFT+ENTER to enter the formula as an array formula.
The formula returns the value 21.

Back to the top

Formula 2: Sum the Digits of a Negative Number

To return the sum of the digits of a negative number contained in cell A11, follow these steps:

Type -234567 in cell A11.
Type the following formula in cell B11:
=SUM(VALUE(MID(A11,ROW(A2:OFFSET(A2,LEN(A11)-2,0)),1)))
Press CTRL+SHIFT+ENTER to enter the formula as an array formula.
The formula returns the value 27.

Back to the top

Explanation of the Formulas

The following information assumes that cell A1 contains the number 849.

   This part of the formula   Does this
------------------------------------------------------------------------


   A1:OFFSET(A1,LEN(A1)-1,0)  Creates a reference of cells going down a
                              column that has the same number of cells as
                              the number in A1 has digits. For example, the
                              formula A1:OFFSET(A1,LEN(A1)-1,0) 
                              returns A1:A3 because LEN(A1)-1 equals 2 and
                              OFFSET(A1,2,0) returns A3.

   ROW()                      Returns the row number of the cell reference.
                              If there is more than one cell in the
                              reference, it returns an array. In this
                              case, ROW(A1:A3) returns {1;2;3}.

   MID()                      Returns a portion of a text string. By using
                              an array for one of the arguments, you can
                              return multiple text strings in an array. For
                              example, consider MID(849,{1;2;3},1) from the
                              above paragraph. This returns
                              {"8";"4":"9"}. Notice that all the numbers
                              are text inside the array.

   VALUE()                    Changes text to numbers. In this example,
                              VALUE({"8";"4":"9"}) returns {8;4;9}. This
                              allows the numbers to be summed.

   SUM({8;4;9})               Returns the final result, 21.

The second formula, which sums the digits of negative numbers, works in a similar manner except that it compensates for the initial minus sign (-) in the number.

Note that in each formula, the "A1" and "A2" are always used, regardless of which cell's digits are being summed.

Back to the top