Sum Numbers In Single Cell

Explanation

The goal is to sum numbers that appear inside a single cell as seen in column B. Technically, the numbers in each cell are a single text string, and the numbers are separated by commas, which is referred to as a “delimiter”. In the current version of Excel, the easiest way to solve this problem is with the TEXTSPLIT function. It is possible to sum numbers separated by other delimiters as well. See below for an example.

Summary

In a nutshell, we use the TEXTSPLIT function to split the numbers up by delimiter, then use VALUE to convert the numbers to true numeric values, and then use the SUM function to get a final sum. In the worksheet shown, the formula in cell B5 is:

=SUM(VALUE(TEXTSPLIT(B5,",")))

Working from the inside out, we start with the TEXTSPLIT function.

TEXTSPLIT function

The TEXTSPLIT function is designed to split a text string by a given delimiter into multiple values. The minimal generic syntax for TEXTSPLIT looks like this:

=TEXTSPLIT(text,delimiter)

The result from TEXTSPLIT is an array of separate values. For example, given the text string “A,B,C” with a comma (",") as a delimiter, TEXTSPLIT returns an array like {“A”,“B”,“C”}:

=TEXTSPLIT("A,B,C",",") // returns {"A","B","C"}

Notice the delimiter is enclosed in double quotes (""). Moving back to the example, with the text “1,2,3” in cell B5, TEXTSPLIT splits the text at each comma and returns an array with 3 values:

=TEXTSPLIT(B5,",") // returns {"1","2","3"}

This is close to what we need, but notice the result from TEXTSPLIT is text . If we try to SUM the output from TEXTSPLIT directly, the SUM function will ignore the text values and return zero:

=SUM(TEXTSPLIT(B5,",")) // returns 0

We need to convert the text values to numeric values before a sum is calculated. To do this, we use the VALUE function.

VALUE function

The VALUE function converts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value. For example, if we provide the text “100”, VALUE returns the number 100:

=VALUE("100") returns 100

We can use VALUE to solve this problem by nesting TEXTSPLIT inside VALUE like this:

=VALUE(TEXTSPLIT(B5,","))
=VALUE({"1","2","3"})
={1,2,3}

VALUE converts each text string to a number and returns the result in an array. As a bonus, VALUE will automatically ignore any space characters that appear in the text.

SUM function

The final step in this problem is to sum the numbers returned by the VALUE function. The final formula in cell D5 is:

=SUM(VALUE(TEXTSPLIT(B5,",")))

TEXTSPLIT splits the text string into separate text values, VALUE converts the text values to numbers, and the result from SUM is 6, the sum of 1+2+3.

Other delimiters

To handle numbers in a cell separated by a different value, you can change the delimiter given to TEXTSPLIT. For example, the same numbers in the worksheet below are separated by the “+” symbol instead of a comma. The formula in cell D5 is:

=SUM(VALUE(TEXTSPLIT(B5,"+")))

<img loading=“lazy” src=“https://exceljet.net/sites/default/files/images/formulas/inline/sum_numbers_in_cell_plus_symbol_delimiter.png" onerror=“this.onerror=null;this.src=‘https://blogger.googleusercontent.com/img/a/AVvXsEhe7F7TRXHtjiKvHb5vS7DmnxvpHiDyoYyYvm1nHB3Qp2_w3BnM6A2eq4v7FYxCC9bfZt3a9vIMtAYEKUiaDQbHMg-ViyGmRIj39MLp0bGFfgfYw1Dc9q_H-T0wiTm3l0Uq42dETrN9eC8aGJ9_IORZsxST1AcLR7np1koOfcc7tnHa4S8Mwz_xD9d0=s16000';" alt=“Delimiter set to a plus symbol “+” - 1”>

When the formula is copied down, the results are the same as the original example above.

Pro tip

There are other ways to convert text values to numbers in Excel. One common method is just to add zero like this:

=SUM(TEXTSPLIT(B5,",")+0)

The result is the same, but we are not using the VALUE function.

Handling text values and errors

If the text in a cell contains text values (i.e. 1,2,3,A) the formula above will return a #VALUE error. This happens because the VALUE function will return a #VALUE error if it can’t convert a value to a number and, when this happens, the SUM function will also return a #VALUE error. One way to handle this problem is to wrap the IFERROR function around VALUE like this:

=SUM(IFERROR(VALUE(TEXTSPLIT(B5,",")),0))

Essentially, we are using IFERROR to convert any errors returned by VALUE to zero. SUM can then sum the zeros along with other numbers without trouble.

Legacy Excel

Older versions of Excel do not provide the TEXTSPLIT function, so there is no direct way to split text values into an array. However, in the Windows version of Excel, you can use a workaround based on the FILTERXML function :

=SUM(FILTERXML("<x><y>"&SUBSTITUTE(B5,",","</y><y>")&"</y></x>","//y"))

In a nutshell, this formula converts the text in B5 to XML by replacing the commas with markup, then it uses FILTERXML to parse the individual numbers into an array. The SUM function then sums the numbers normally. FILTERXML automatically converts the numbers to numeric values in the process, so the VALUE function is not needed. Detailed explanation here .

Note: FILTERXML is only available in Windows Excel.

Explanation

In this example, the goal is to sum the largest n values in a set of data, where n is a variable that can be easily changed. For convenience, the range B5:B16 is named data . At a high level, the solution breaks down into two steps: (1) extract the n largest values from the data set and (2) sum the extracted values. There are several ways to approach this problem depending on what version of Excel is available. Regardless of the approach, all solutions below depend on the LARGE function.

LARGE function

The LARGE function is designed to return the nth largest value in a range. For example:

=LARGE(range,1) // 1st largest
=LARGE(range,2) // 2nd largest
=LARGE(range,3) // 3rd largest

Normally, LARGE returns just one value. However, if you supply an array like {1,2,3} to LARGE as the second argument, k , LARGE will return an array of results instead of a single result. For example, the formula below will return the 3 largest values in B5:B16:

=LARGE(data,{1,2,3}) // returns {70;65;55}

Note that the result from LARGE is an array . By nesting the LARGE function side the SUM function, we can get a sum of the 3 largest three values in data :

=SUM(LARGE(data,{1,2,3})) // returns 190

The SUM function returns a result of 190, the sum of 70, 65, and 55. In older versions of Excel, you might have to use the SUMPRODUCT function instead of the SUM function, for reasons described here .

Given the formula outlined above, the challenge becomes how best to create the array needed to extract the top n values from a set of data. This depends on what Excel version is available.

Current Excel

The latest version of Excel supports dynamic array formulas and provides a number of functions that make working with arrays easier. One of these functions is SEQUENCE , which is designed to generate arrays on the fly. In the example shown, the formula in E5 uses SEQUENCE to create a numeric array based on the value for n in cell D5

=SUM(LARGE(data,SEQUENCE(D5)))

Working from the inside out, the SEQUENCE function is configured with the rows argument set to cell D5. With the number 3 in D5, sequence generates a sequential array like this:

SEQUENCE(D5) // returns {1;2;3}

The numeric array is returned directly to the LARGE function for the k argument:

=SUM(LARGE(data,{1;2;3}))

And the solution proceeds as outlined above:

=SUM(LARGE(data,{1;2;3}))
=SUM({70;65;55})
=190

The final result is 190, as shown in the worksheet above.

Legacy Excel

In older versions of Excel that do not provide dynamic array support, or the SEQUENCE function, we need to take a different approach. One simple solution is to hardcode the numeric array given to LARGE as an array constant and switch to the SUMPRODUCT function :

=SUMPRODUCT(LARGE(data,{1,2,3}))

This formula works fine in older versions of Excel, but because the array is hardcoded inside LARGE, it is not a dynamic solution that uses the value for n in cell D5. In addition, as n becomes larger, it gets tedious to enter longer array constants like {1,2,3,4,5,6,7,8,9}, etc. To workaround this problem, you can use the more advanced formula below.

Dynamic n

The classic solution for creating a numeric array in older versions of Excel is to use the ROW and INDIRECT functions. For example, to generate a numeric array from 1 to 10, you can use a formula like this:

=ROW(INDIRECT("1:10")) // returns {1;2;3;4;5;6;7;8;9;10}

The INDIRECT function converts the text string “1:10” to the range 1:10, which is returned to the ROW function. The ROW function returns the 10 row numbers that correspond to 1:10 in an array like this:

{1;2;3;4;5;6;7;8;9;10}

Note this is actually a vertical array , as indicated by the semicolons (;) but the LARGE function will happily accept a vertical or horizontal array for k . To adapt this approach in the worksheet as shown, we need to adjust the formula to concatenate the value for n to the string “1:” inside of INDIRECT like this:

=SUMPRODUCT(LARGE(data,ROW(INDIRECT("1:"&D5))))

This formula is now dynamic. When the value for n is changed, ROW and INDIRECT will spin up a new array that reflects the current value, and LARGE will extract the top n values as before, and SUMPRODUCT will return a sum.