text: # Microsoft KB Archive/108107
  
  (diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
   Knowledge Base
  
  # Formula to Sum Digits of a Number
  
  Article ID: 108107
  
  APPLIES TO
  
  • Microsoft Excel 97 Standard Edition
  
  ## SUMMARY
  
  Although there is currently no built-in formula that finds the sum of the digits of a number in Microsoft Excel, you can use the formula in the "More Information" section in this article to do this.
  
  The following formula returns the sum of the digits of a positive number contained in cell A10:
  
  ```   =SUM(VALUE(MID(A10,ROW(\$A\$1:OFFSET(\$A\$1,LEN(A10)-1,0)),1)))
  ```
  
  The following formula returns the sum of the digits of a negative number contained in cell A11:
  
  ```   =SUM(VALUE(MID(A11,ROW(\$A\$2:OFFSET(\$A\$2,LEN(A11)-2,0)),1)))
  ```
  
  NOTE: The above formulas must be entered as array formulas. To enter a formula as an array formula in Microsoft Excel for Windows or Microsoft Excel for OS/2, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+RETURN.
  
  ### Explanation of the Formula
  
  The following information assumes that cell A1 contains the number 849.
  
  ```   This part of the formula  Does this
  
  ------------------------------------------------------------------------
  
  A\$1:OFFSET(\$A\$1,LEN(A1)-  Creates a reference of cells going down a
  1,0)                      column that has the same number of cells as
  the number in A1 has digits. For example, the
  formula \$A\$1:OFFSET(\$A\$1,LEN(A1)-1,0) would
  return \$A\$1:\$A\$3 since LEN(A1)-1 equals 2 and
  OFFSET(\$A\$1,2,0) returns \$A\$3.
  
  ROW()                     Returns the row number of the cell reference.
  If there is more than one cell in the
  reference, it will return an array. In this
  case, ROW(\$A\$1:\$A\$3) returns {1;2;3}.
  
  MID()                     Returns a portion of a text string. By using
  an array for one of the arguments, we can
  return multiple text strings in an array. For
  example, consider MID(849,{1;2;3},1) from the
  above paragraph. This will return
  {"8";"4":"9"}. Notice that all the numbers are
  text inside the array.
  
  VALUE()                   Will change 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 the same manner except that it compensates for the initial minus sign (-) in the number.
  
  NOTE: In each formula, the "\$A\$1" and "\$A\$2" are always used, regardless of which cell's digits are being summed.
  
  ## REFERENCES
  
  "Function Reference," version 4.0, pages 274, 299-300, 365-366, 423, 444.
  
  Additional query words: 2.0 2.10 4.00a 5.00a 5.00c 7.00a 97 XL97 XL7 XL5 XL4 XL3 XL
  
  Keywords: kbhowto KB108107
explanation: The document is a technical article from the Microsoft Knowledge Base that provides a formula to sum the digits of a number in Microsoft Excel. It includes formulas for both positive and negative numbers and explains how these formulas work. The article does not contain a question or an answer to a question.
distilabel_metadata.content: Evaluate the user given text extract and determine if it contains a math question and optionally and answer to this question.
  
  Output rules:
  
  1. Briefly describe the document and mention if it contains a question or a question and answer.
  2. Finalize the output with: Label: no-question,question,question-and-answer,multiple-question,multiple-question-and-answer
distilabel_metadata.role: system
distilabel_metadata.content: # Microsoft KB Archive/108107
  
  (diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
   Knowledge Base
  
  # Formula to Sum Digits of a Number
  
  Article ID: 108107
  
  APPLIES TO
  
  • Microsoft Excel 97 Standard Edition
  
  ## SUMMARY
  
  Although there is currently no built-in formula that finds the sum of the digits of a number in Microsoft Excel, you can use the formula in the "More Information" section in this article to do this.
  
  The following formula returns the sum of the digits of a positive number contained in cell A10:
  
  ```   =SUM(VALUE(MID(A10,ROW(\$A\$1:OFFSET(\$A\$1,LEN(A10)-1,0)),1)))
  ```
  
  The following formula returns the sum of the digits of a negative number contained in cell A11:
  
  ```   =SUM(VALUE(MID(A11,ROW(\$A\$2:OFFSET(\$A\$2,LEN(A11)-2,0)),1)))
  ```
  
  NOTE: The above formulas must be entered as array formulas. To enter a formula as an array formula in Microsoft Excel for Windows or Microsoft Excel for OS/2, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+RETURN.
  
  ### Explanation of the Formula
  
  The following information assumes that cell A1 contains the number 849.
  
  ```   This part of the formula  Does this
  
  ------------------------------------------------------------------------
  
  A\$1:OFFSET(\$A\$1,LEN(A1)-  Creates a reference of cells going down a
  1,0)                      column that has the same number of cells as
  the number in A1 has digits. For example, the
  formula \$A\$1:OFFSET(\$A\$1,LEN(A1)-1,0) would
  return \$A\$1:\$A\$3 since LEN(A1)-1 equals 2 and
  OFFSET(\$A\$1,2,0) returns \$A\$3.
  
  ROW()                     Returns the row number of the cell reference.
  If there is more than one cell in the
  reference, it will return an array. In this
  case, ROW(\$A\$1:\$A\$3) returns {1;2;3}.
  
  MID()                     Returns a portion of a text string. By using
  an array for one of the arguments, we can
  return multiple text strings in an array. For
  example, consider MID(849,{1;2;3},1) from the
  above paragraph. This will return
  {"8";"4":"9"}. Notice that all the numbers are
  text inside the array.
  
  VALUE()                   Will change 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 the same manner except that it compensates for the initial minus sign (-) in the number.
  
  NOTE: In each formula, the "\$A\$1" and "\$A\$2" are always used, regardless of which cell's digits are being summed.
  
  ## REFERENCES
  
  "Function Reference," version 4.0, pages 274, 299-300, 365-366, 423, 444.
  
  Additional query words: 2.0 2.10 4.00a 5.00a 5.00c 7.00a 97 XL97 XL7 XL5 XL4 XL3 XL
  
  Keywords: kbhowto KB108107
distilabel_metadata.role: user
distilabel_metadata.raw_output_q_a_detector_0: The document is a technical article from the Microsoft Knowledge Base that provides a formula to sum the digits of a number in Microsoft Excel. It includes formulas for both positive and negative numbers and explains how these formulas work. The article does not contain a question or an answer to a question.
  
  Label: no-question