Spreadsheet Practice

How to sum the digits of a number in Excel

Formula (Excel and Google Sheets)

=SUMPRODUCT(--MID(A2,SEQUENCE(LEN(A2)),1))

Split the number into single characters and add them: =SUMPRODUCT(--MID(A2,SEQUENCE(LEN(A2)),1)).

Result on the example
one result per row in B2, filled down to B6
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in B2, filled down to B6. Change any input and the result updates, or edit the formula to experiment.

How it works

LEN counts the digits (4 for 4721), SEQUENCE lists the positions 1 to 4, and MID takes one character from each position: "4", "7", "2", "1". The double minus turns those characters into numbers and SUMPRODUCT adds them.

=SUMPRODUCT(--MID(A2,SEQUENCE(LEN(A2)),1))

Result14

  1. A24721

    The value in A2.

  2. LEN(A2)4

    LEN: Number of characters.

    • textA24721
  3. SEQUENCE(LEN(A2)){4 rows × 1 col} 1, 2, 3, 4

    SEQUENCE: A grid of sequential numbers. Spills.

    • rowsLEN(A2)4
  4. MID(A2,SEQUENCE(LEN(A2)),1){4 rows × 1 col} "4", "7", "2", "1"

    MID: Characters from the middle of text.

    • textA24721
    • start_numSEQUENCE(LEN(A2)){4 rows × 1 col} 1, 2, 3, 4
    • num_chars1
  5. -MID(A2,SEQUENCE(LEN(A2)),1){4 rows × 1 col} -4, -7, -2, -1

    Makes the value negative.

  6. --MID(A2,SEQUENCE(LEN(A2)),1){4 rows × 1 col} 4, 7, 2, 1

    Makes the value negative.

  7. SUMPRODUCT(--MID(A2,SEQUENCE(LEN(A2)),1))14

    SUMPRODUCT: Multiplies arrays row by row and adds the results.

    • array1--MID(A2,SEQUENCE(LEN(A2)),1){4 rows × 1 col} 4, 7, 2, 1

In Google Sheets

The same formula works in Google Sheets: =SUMPRODUCT(--MID(A2,SEQUENCE(LEN(A2)),1)). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUM(--MID(A2,SEQUENCE(LEN(A2)),1))Excel only14, 25, 7…Excel 365 handles the array inside SUM directly.

Common mistakes

  • A minus sign or decimal point can't be turned into a number and gives #VALUE!. Use ABS and SUBSTITUTE(A2,".","") first.
  • SEQUENCE needs Excel 2021 or 365. In older Excel use ROW(INDIRECT("1:"&LEN(A2))) for the positions.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Sum the digits of a number

In B2, add up the digits of the number in A2, then fill down to B6.

Questions

How do I reduce it to a single digit?

For a positive whole number, the repeated digit sum is =1+MOD(A2-1,9).

How do I sum the digits of a text code like "AB-4721"?

Strip the non-digits first, or sum only numeric characters: =SUMPRODUCT(IFERROR(--MID(A2,SEQUENCE(LEN(A2)),1),0)).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.