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.

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Hyperlink to Jump to the Last Used Row in Excel

In a vertical range, you can create a hyperlink to jump to the last...

Find the Last Used Row’s Last Value Address in Excel

In a large vertical dataset in Excel, how do you find the cell address...

Find the Last Used Row Number in Excel

When working with large datasets such as sales records, purchase data, or bills of...

More like this

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

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

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.