# How to sum numbers separated by commas in one cell in Excel

Split the text and add the pieces: `=SUM(--TEXTSPLIT(B2,","))` in Excel 365, or `=SUM(SPLIT(B2,","))` in Google Sheets.

**Excel:** `=SUM(--TEXTSPLIT(B2,","))` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=SUM(SPLIT(B2,","))`

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Order | Box weights (kg) | Total kg |
| SO-881 | 12.5,8,15.25 |  |
| SO-882 | 4.75,6 |  |
| SO-883 | 20,18.5,9,11 |  |
| SO-884 | 7.2 |  |
| SO-885 | 3.5,3.5,3.5 |  |

Formula in C2, filled to C6: `=SUM(--TEXTSPLIT(B2,","))`

Result: 35.75; 10.75; 58.50; 7.20; 10.50

## How it works

TEXTSPLIT breaks "12.5,8,15.25" into three pieces of text. The double minus turns them into numbers, because SUM skips numbers stored as text. Google Sheets' SPLIT already returns numbers, so SUM can add them directly. A cell with a single number, like 7.2, works too.

## Other ways

- `=SUM(VALUE(TEXTSPLIT(B2,",")))`: VALUE does the same conversion as the double minus and reads more clearly.

## Common mistakes

- Without `--`, `=SUM(TEXTSPLIT(B2,","))` returns 0 because every piece is text.
- Spaces after commas ("12.5, 8") still convert, but other separators like semicolons need their own delimiter: `TEXTSPLIT(B2,{",",";"})`.
- TEXTSPLIT is Excel 365 only. In older versions, split the column with Data > Text to Columns first.

## Questions

**How do I count the numbers in the cell?**

Use `=COUNTA(TEXTSPLIT(B2,","))`, or in any version `=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1`.

**How do I average them?**

Swap SUM for AVERAGE: `=AVERAGE(--TEXTSPLIT(B2,","))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-numbers-separated-by-commas-in-a-cell