Sum, Count & Cumulative Sum of Comma-Separated Values in Google Sheets

Published on

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:

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:

102530343959

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:

111211

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:

TaskFormula
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.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.