If you store numbers as comma-separated values in a single cell, you might wonder how to sum, count, or calculate cumulative totals without splitting them into separate columns. This tutorial will show you how to do it using formulas—without helper columns.
Common use cases include:
- Student scores recorded in one cell (e.g.,
"70, 75, 80, 80, 85, 90"). - Sales transactions where amounts are stored as
"1500.00, 2000.75, 2500.50". - Expense tracking with multiple values in a single cell.
Let’s explore formulas to perform these calculations efficiently.
How to Sum Comma-Separated Values in Google Sheets
Assume the following numbers are stored in cell A1:
10, 15, 5, 4, 5, 20
To calculate the sum of these values, use:
=SUM(SPLIT(A1, ", ", FALSE))Explanation:
- The SPLIT function separates values based on
", "(comma + space). - The SUM function then totals the extracted numbers.
Tip: If values are separated by just a comma (without a space), modify the formula as follows:
=SUM(SPLIT(A1, ",", FALSE))How to Count Comma-Separated Values in Google Sheets
To count how many numbers are in the cell, replace SUM with COUNT:
=COUNT(SPLIT(A1, ", ", FALSE))This formula returns 6, as there are six values in A1.
Formula for Cumulative Sum of Comma-Separated Values
A cumulative sum (running total) is useful when tracking progress over time, such as sales growth or expense accumulation.
Use the SCAN function to calculate the cumulative sum:
=SCAN(0, SPLIT(A1, ", ", FALSE), LAMBDA(acc, col, acc+col))Output:
For 10, 15, 5, 4, 5, 20, this returns:
| 10 | 25 | 30 | 34 | 39 | 59 |
Tip: Want the result in a single column? Use:
=TOCOL(SCAN(0, SPLIT(A1, ", ", FALSE), LAMBDA(acc, col, acc+col)))Formula for Cumulative Count of Comma-Separated Values
A cumulative count (running count) shows how frequently a value appears up to a given position. This is helpful when analyzing repeated scores, sales, or recurring events.
Example:
For the student’s test scores:
70, 75, 80, 80, 85, 90
A cumulative count would return:
| 1 | 1 | 1 | 2 | 1 | 1 |
Formula:
=ArrayFormula(LET(spt, SPLIT(A1, ", ", FALSE), COUNTIFS(spt, spt, SEQUENCE(1, COLUMNs(spt)), "<="&SEQUENCE(1, COLUMNs(spt)))))How It Works:
SPLIT(A1, ", ", FALSE)separates numbers.- COUNTIFS counts occurrences up to each position.
- LET improves performance by storing the split array in
spt.
To return results in a column instead of a row, wrap with TOCOL:
=ArrayFormula(LET(spt, SPLIT(A1, ", ", FALSE), TOCOL(COUNTIFS(spt, spt, SEQUENCE(1, COLUMNs(spt)), "<="&SEQUENCE(1, COLUMNs(spt))))))Summary of Formulas:
| Task | Formula |
| Sum Comma-Separated Values | =SUM(SPLIT(A1, ", ", FALSE)) |
| Count Comma-Separated Values | =COUNT(SPLIT(A1, ", ", FALSE)) |
| Cumulative Sum | =SCAN(0, SPLIT(A1, ", ", FALSE), LAMBDA(acc, col, acc+col)) |
| Cumulative Count | =ArrayFormula(LET(spt, SPLIT(A1, ", ", FALSE), COUNTIFS(spt, spt, SEQUENCE(1, COLUMNs(spt)), "<="&SEQUENCE(1, COLUMNs(spt))))) |
Final Thoughts
By using SUM, COUNT, and cumulative functions, you can analyze comma-separated values without needing extra columns. The SPLIT function is the key to unlocking powerful calculations in Google Sheets.
