You are currently offline, waiting for your internet to reconnect

XL2000: Formula to Sum Digits of a Number

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.
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:
  1. Start Excel 2000.
  2. Type 123456 in cell A10.
  3. Type the following formula in cell B10:
    =SUM(VALUE(MID(A10,ROW(A1:OFFSET(A1,LEN(A10)-1,0)),1)))
  4. Press CTRL+SHIFT+ENTER to enter the formula as an array formula.
  5. 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:
  1. Type -234567 in cell A11.
  2. Type the following formula in cell B11:
    =SUM(VALUE(MID(A11,ROW(A2:OFFSET(A2,LEN(A11)-2,0)),1)))
  3. Press CTRL+SHIFT+ENTER to enter the formula as an array formula.
  4. The formula returns the value 27.

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 asimilar manner except that it compensates for the initial minus sign (-) inthe number.

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

Article ID: 214053 - Last Review: 09/27/2003 13:48:19 - Revision: 3.1

  • Microsoft Excel 2000 Standard Edition
  • kbhowto kbinfo KB214053
Feedback