Microsoft KB Archive/214053

= XL2000: Formula to Sum Digits of a Number =

Article ID: 214053

Article Last Modified on 9/27/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



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.



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.

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.</li> Type the following formula in cell B11:

=SUM(VALUE(MID(A11,ROW(A2:OFFSET(A2,LEN(A11)-2,0)),1)))

</li> Press CTRL+SHIFT+ENTER to enter the formula as an array formula.</li> The formula returns the value 27.</li></ol>

Explanation of the Formulas
The following information assumes that cell A1 contains the number 849. <pre class="fixed_text">  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.

Keywords: kbhowto kbinfo KB214053

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.