Math & Trig
Sum or average numbers stored inside a single comma-separated cell
Total, average, or count comma- or semicolon-delimited numbers in one cell ("120, 85, 45, 150" -> 400) using TEXTSPLIT and numeric coercion.
Why SUM(TEXTSPLIT(...)) returns 0 without numeric coercion
TEXTSPLIT(A2, ",") always outputs an array of TEXT strings {"120", " 85", " 45", " 150"}. Because spreadsheet SUM and AVERAGE ignore text strings inside arrays, SUM(TEXTSPLIT(A2, ",")) evaluates to 0!
Multiplying by 1 (1 * TEXTSPLIT(A2, ",", , TRUE)) coerces every non-empty token into a genuine number so SUM, AVERAGE, MIN, and MAX work immediately.
| Delimited Cell (A) | Item Count | Total Sum | Average |
|---|---|---|---|
| 120, 85, 45, 150 | 4 | 400 | 100 |
| 19.50; 30.50; 25.00 | 3 | 75 | 25 |
| (blank) | 0 | 0 | Undefined (No numbers) |
Blank-safe delimited sum and average formulas
Support both commas and semicolons simultaneously by passing {",", ";"} as the delimiter array and TRUE for ignore_empty.
=IF(OR(A2="",TRIM(A2)=""), 0, SUM(1 * TEXTSPLIT(A2, {",",";"}, , TRUE)))Average delimited numbers in a single cell
Coerces tokens with 1 * before averaging.
=IF(OR(A2="",TRIM(A2)=""), "No numbers", AVERAGE(1 * TEXTSPLIT(A2, {",",";"}, , TRUE)))Inputs and interpretation
Tokens must be valid numbers in the chosen locale. Commas and semicolons are separators here, so decimal commas or thousands separators conflict with this format. Whitespace-only tokens are not the same as missing tokens. This does not parse quoted CSV fields. A blank total is defined as zero; its mean is undefined.
Try the example
Use the original values or change them to test your understanding.