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 in-built SUM() function over texts to get their sum because the SUM() will always return us a value 0.

We can observe from the image given above, when we use the =SUM(B2:B6), to sum text values then we get the result as 0. From this we now have an understanding that to add numbers using the SUM() function, we need to have numerical values i.e., the number data type for each element in the cell range. To convert these texts back to numbers we need to perform two steps:

Step 1: Remove the units (here, km) from the elements.

It can be done using the SUBSTITUTE() function in Microsoft Excel. The SUBSTITUTE() function in Excel is used to substitute specific values inside a text string with a new value. The syntax for the SUBSTITUTE() function is given as:

=SUBSTITUTE(original_text, old_text, new_text, [instance_num])

where original_text is the element/record value, it is a required parameter, old_text is the string value you want to substitute inside the original_text, it is a required parameter, new_text is the string value that will replace the old_text, it is a required parameter, and instance_num is an optional parameter that specifies the appearance number of old_text in the original_text that we want to substitute with the new_text.

Example:

Here, we used the SUBSTITUTE() function to substitute the text “km” with nothing, therefore, used “” with no characters inside the double quotes. Thus, removing “km” from the entire text and getting 20 as the output which is still a text value. 

Step 2: Convert the text back to the number data type.

Now we can perform any mathematical operation with this text value to convert it back to number type hence we can add 0 to the text thus it will not affect its value.

Now that we know how we can convert these text values back to numbers we will perform this operation for the whole-cell range as:

Step 3: Using SUM() function to sum all the numbers along with their units 

After converting all the text back to number format we can use the basic SUM() function to add these numbers as can be seen from the image given below. We use the SUM() function on values from C2:C6 to get the final result.

So this is how we correctly sum numbers with units in a range in Excel.