Skip to content

Sum Comma-Separated Values in Sheets

In this article, I will show you how to sum comma-separated values in Google Sheets. It’s not that complicated. You only need two functions to do it!

The Setup

So here is the problem. You have numbers separated by commas listed in a single cell. And this goes on for several rows.

Having comma-separated values like this does not make the data easy to deal with. Specifically, getting a total for each set of values isn’t immediately intuitive.

So how do you do it?

The Solution

To sum all of the comma-separated values from a single cell, you will need two functions.

The SPLIT function will take the values from a single cell and split them into an array of numbers. The SUM function will then add up all the numbers within that array.

Using this logic, you enter the following formula in the cell next to the first cell containing the comma-separated numbers.

=SUM(SPLIT(B2,","))

If we break this formula down:

The SPLIT function splits data into an array of numbers based on a specified delimiter. In this case, it is a comma.

=SUM(SPLIT(B2,",")) → =SUM({202,333,439...343})

The SUM function then adds all of the numbers in the array to give us the total.

With the formula written for the first cell, you can finally use the fill handle to drag the formula down for all the other cells containing comma-separated values.

And that is the easiest way to sum comma-separated values in Google Sheets.

This Post Has 3 Comments

  1. How can I make this function run automatically in a spreadsheet with an arrayformula? I want to use a google form where every time data is entered, in the spreadsheet will do an automatic calculation using an arrayformula.

Leave a Reply

Your email address will not be published. Required fields are marked *

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

Back To Top