How to Sum Numbers With Commas in a Single Excel Cell?
Last Updated :
22 Mar, 2022
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:
Similar Reads
How to Correctly Sum Numbers With Units in a Range in Excel? In Excel, we can sum numbers in a range which are given along with their units. When we place numbers along with their units inside Excel then they are treated as text by Excel implicitly in the backend. Now, the problem that arises here is we cannot directly apply any mathematical function like the
3 min read
How to Sum Diagonal Cells in a range in Excel? The calculation of matrices is one of the most difficult numerical computation tasks. You may need to add values diagonally in a table when performing mathematical computations. Excel allows you to sum diagonal numbers without having to add the values one by one. We'll go through how to sum cells di
6 min read
How to read numbers with a comma as decimal separator in R? Handling numeric data correctly is important for accurate data analysis, and one common issue is dealing with different decimal separators. While the dot (.) is commonly used as a decimal separator in English-speaking countries, many European and South American countries use the comma (,). This arti
5 min read
How To Merge Cells in Excel Without Losing Data Merging cells in Excel is a simple but powerful way to organize and format your spreadsheets. Whether you're looking to combine two cells, center text across a group of cells, or merge multiple rows or columns, knowing how to merge cells in Excel is a must-have skill. In this guide, weâll walk you t
11 min read
How to Start a New Line in Excel Cell: 4 Easy Methods When you are using MS Excel for storing and manipulating text data sometimes starting a new line within a cell in Excel can be incredibly useful when you want to enter multiple pieces of information or data in a single cell without cluttering your spreadsheet. Now, if you are looking for a guide to
6 min read
How to Calculate a Five Number Summary in Excel? In statistics, the five-number summary is mostly used as it gives a rough idea about the dataset. It is basically a summary of the dataset describing some key features in statistics. The five key features are : Minimum value: It is the minimum value in the data setFirst Quartile, Q1: It is also know
3 min read
How to Put Count and Percentage in One Cell in Excel? In the real world, sometimes the user would like to present two or more values in a single cell. For eg in the sales report, we might need sales and the respective %share in the same cell. So in this article, we will learn how to display count and percentage in the same cell with the help of an exam
2 min read
How to Concatenate with a Line Break in Excel? Excel is a powerful tool to store, manage, and visualize large amounts of data. This tool is usually used by accounting professionals for the analysis of financial data but can be used by anyone to manage large data. Data is entered into the rows and columns. And then, various formulas and functions
3 min read
How to Sum a Column in Excel: 3 Methods Knowing how to sum a column in Excel is essential for managing budgets, financial reports, and large datasets. Whether youâre calculating sales totals, adding expenses, or analyzing business data, Excel provides multiple ways to add up a column efficiently. Many users wonder "how to add columns in E
5 min read