With the help of the Split function, we can separate comma separated values in a cell into multiple cells. That same Split function you can combine with other functions to find the sum and
Before going to the formulas, let me show you how to separate comma delimited values in Google Doc Sheets.
The comma delimited values may or may not have leading/trailing spaces.
Comma Separated Value without Space:
10,15,5,4,5,20
Comma Separated Value with Space:
10, 15, 5, 4, 5, 20
My formula works in both the above two types of comma separated values without any flaws.
How to Separate Comma Delimited Values in Google Sheets?
Assume the above values are in cell A1. Then use this formula in any other cell. But do keep sufficient blank cells for the formula to expand the results. All the split numbers will be in individual cells.
=split(A1,",")How to Sum, Count, Cumulative Sum Comma Separated Values
First let me show you the formula outputs:
In this, for sum and count, you just need to wrap the split formula with the functions SUM and COUNT respectively. See the example to the both below.
How to Sum Comma Separated Values in Google Doc Sheets?
Formula in Cell B2:
=sum(split(A1,","))How to Count Comma Separated Values in Google Doc Sheets?
Formula in Cell D2:
=COUNT(split(A1,","))Cumulative Sum Comma Separated Values in Google Sheets
This is a broad topic because before going to look into the formula you must know how to calculate cumulative sum in Google Sheets. Then we can easily incorporate the comma separated values into the formula.
Cumulative Sum (Array Formula) in Google Sheets
Actually, there are two types of array formulas that you can use to calculate cumulative sum in Google Sheets.
- SUMIF based array formula – Works in Google Sheets without a helper column but in Excel with a helper column.
- MMULT based array formula – Works both in Google Sheets and Excel without any helper column.
In the above two, the Sumif based cumulative sum (running total) array formula is the simplest one. But we can’t use that with comma separated values.
Must Check: Running Total Array Formula in Google Sheets (Sumif).
Then the only option left with us is the MMULT one. I have earlier posted a tutorial on the use of MMULT to calculate running total in Excel. Thankfully that works in Google Sheets too.
In the Excel tutorial, I have just provided the formula and that’s enough to get the cumulative sum.
See that formula below and this time I’ll try to explain how to develop it. The below formula is for the above sample data. That means for the numbers in the range A1: A7
=ArrayFormula(MMULT(IF(ROW(A1:A7)>=TRANSPOSE(ROW(A1:A7))=TRUE,1,0),A1:A7))Once learned, you can easily change the numbers in A1: A7 with the comma separated values.
Cumulative Sum Array Formula Explanation
To understand the formula you must first learn the MMULT function in Google Sheets. Please learn, or at least try to understand the use of the MMULT using my tutorial that you can get from my function guide. Then come back.
Syntax:
MMULT(matrix1, matrix2)In this, the matrix2 is our numbers in the range A1: A7. So what is the matrix1?
The formula that underlined in red color forms the matrix1. You can see two Row formulas within the matrix1.
The first Row formula returns 1 to 7 sequential numbers vertically. The second Row formula returns the number 1 to 7 horizontally because there is the Transpose function with the Row.
Here is the explanation of what happens within the underlined (red) matrix1 formula.
Just assume I have the first Row formula in cell E1 and the second
Now enter the below formula in cell N1 then drag to 7 columns right (until cell T1) and 7 columns down (until cell N7).
=if($E1>=F$1,1,0)You will get the matrix1 as below.
This is what happens within the matrix1 formula. It simply generates a matrix as above.
Cumulative Sum Comma Separated Values (Array Formula)
In the above formula, you just need to change the matrix2 with the below formula to get the cumulative sum or running total of comma separated values.
transpose(split(A1,","))Here is the ‘final’ formula:
=ArrayFormula(MMULT(IF(ROW(A1:A7)>=TRANSPOSE(ROW(A1:A7))=TRUE,1,0),transpose(split(A1,","))))This formula would work if the count of comma delimited numbers is 7 like 10, 15, 5, 4, 5, 20, 100.
You can make the above formula to accept any number of comma-separated numbers by simply changing the row formula as below.
ROW(indirect("A1:A"&COUNT(split(A1,",")))If you compare this formula with the earlier row formula, you can see that the cell reference A1: A7 is replaced by the Indirect formula. This is to automate the number 7 in the A7.
After this modification the formula would look like this.
=ArrayFormula(MMULT(IF(ROW(indirect("A1:A"&COUNT(split(A1,","))))>=TRANSPOSE(ROW(indirect("A1:A"&COUNT(split(A1,",")))))=TRUE,1,0),transpose(split(A1,","))))Use the above ArrayFormula to get the cumulative sum of comma-separated values in Google Sheets.
That’s all. Enjoy.
