Sum numbers in single cell

Sum numbers in single cell

To sum numbers in a single cell separated by a given delimiter, you can use a formula based on the TEXTSPLIT and VALUE functions.

How to sum numbers in a single cell?

Steps to sum numbers that appear inside a single cell:

  1. Select cell D3
  2. Type =SUM(VALUE(TEXTSPLIT(B3, “,”))) in the cell where you want the total.
  3. Press Enter.
  4. The formula will split the numbers in the cell by the comma delimiter, convert them to values, and sum them.

Example

In this example, we have a dataset in the range B3:B10 containing text strings with numbers separated by commas. The goal is to calculate the total of the numbers within each cell and display the results in the corresponding cells in column D.

The formula in cell D3 uses the TEXTSPLIT function to separate the numbers based on the comma delimiter and the VALUE function to convert the text into numeric values. Finally, the SUM function is applied to calculate the total for each row, displaying the sum in column D.

Formula:

=SUM(VALUE(TEXTSPLIT(B3, “,”))),

Sum numbers in single cell

Copy the formula down to get the summary of comma-separated values.

Explanation

Evaluate the formula from the inside out!

TEXTSPLIT(B3, “,”): The TEXTSPLIT function divides the text in cell B3 into separate values based on a specified delimiter, which in this case is a comma (,). When you apply TEXTSPLIT(B3, “,”) to a cell like B3 that contains “50,20,10,30,40,” it will split this into an array: {50, 20, 10, 30, 40}. Each number in the text string becomes an individual element in this array, making it possible to perform calculations on them.

VALUE(TEXTSPLIT(B3, “,”)): VALUE converts text entries that look like numbers into numeric values. After TEXTSPLIT creates an array of text strings ({“50”, “20”, “10”, “30”, “40”}), VALUE transforms each element into a number, resulting in {50, 20, 10, 30, 40}. This conversion is important because SUM can only add actual numbers, not text.

The TEXTSPLIT function divides the text

SUM(VALUE(TEXTSPLIT(B3, “,”))): SUM function adds up all the values in the array created by VALUE(TEXTSPLIT(B3, “,”)). For example, B3 with the values {50, 20, 10, 30, 40}, SUM computes 50 + 20 + 10 + 30 + 40 = 150. The result is 150.

Download the practice file.

You can find other methods here if you have a complex dataset. These formulas use the FILTERXML and SUMCSV functions.