In Excel, we can add different comma-separated numbers that are present in the same cell of an excel sheet. Look at the example below, there are 5 comma-separated numbers in cell A2 and we have to sum them up.
Comma-separated values in a single cell
Let us see the various ways in which we can add them.
Sum numbers using the Formula
The simplest way to add comma-separated values that are present in a single cell is by using the following formula:
=SUM(IF(ISERR(VALUE(MID(A2,ROW($A$1:OFFSET($A$1,LEN(A2)-1,0)),1))),0,VALUE(MID(A2,ROW($A$1:OFFSET($A$1,LEN(A2)-1,0)),1))))
Formula Explanation
- This is an array formula and hence it executes with the help of Shift + Ctrl + Enter keys.
- A2 is the cell in which comma separated values are present that we have to add.
- The LEN function calculates the length\number of values present in a cell.
- The OFFSET function returns a range, that can be a number of rows or columns.
- The MID function extracts specific characters from a string. Like in this case, those characters can be numbers. This way the numbers and commas can be segregated. After segregation, the numbers can be added independently.
- The ISSERR function checks errors in the initial expression.
- The IF function check all the conditions present inside it and if they evaluate to true, then the SUM function proceeds to adding the numbers.
So, write this formula in the cell where you want to see the result and then press Shift + Ctrl + Enter key.
The result after pressing the Shift + Ctrl + Enter key is:
But this method has a disadvantage. We can only add digits that have a single digit. See, how 11 and 12 have been added up to 5 instead of 23 in the below example.
This problem can be solved if we first split the numbers using commas.
Sum numbers Using Text to Columns
We can also find the sum of numbers using text to columns. In this approach, we first apply the Text to Columns function so that we can split numbers by comma. Then, we can sum these separate numbers. These the steps to be followed:
Step 1: Select the cell in which these comma-separated numbers are present. Then, go to the Text to Columns option present on the Data Tools tab. A dialog box will appear. Select Delimited from it.
Step 2: Now, click next and select comma from the delimiters section. Again click next.
Step 3: Now select a cell where you want to place the result after performing a split. Here, we have chosen $E$5. After this, click Finish.
See the result of this step:
Step 4: Now simply use the formula to sum these numbers. The formula is: =SUM(cell _start:cell_end)
On clicking enter, this will simply return the output as follows: