If you are looking for **Excel SUM cells with text and numbers**, then you are in the right place. Adding cells in Excel is a simple yet powerful task. There are several ways to sum cells with numeric data in Excel. In this article, we’ll discuss Excel SUM cells with text and numbers.

## Download Practice Workbook

## 3 Ways to Sum Cells with Text and Numbers in Excel

Excel offers different ways to SUM cells with text and numbers. All of them are very easy to use.

### 1. Using SUBSTITUTE Function to SUM Cells with Text and Numbers

The formula that we used here uses two Excel functions, the** SUBSTITUTE **function, and the **SUM **function. Firstly, the formula separates the text from the numeric values using the **SUBSTITUTE **function, and secondly, adds them up using the **SUM **function.

Eventually, the syntax of the **SUBSTITUTE **function is as follows:

`=SUBSTITUTE (text, old_text, new_text, [instance])`

**text:** The cell or cells to modify.**old_text:** The old text to replace.**new_text:** The new text to replace with.**instance [Optional]:** The instance to replace. By default, all instances will be replaced.

You can learn about this function in detail by reading this **documentation** from Microsoft.

Importantly, the syntax of the **SUM **function is as follows:

`=SUM(number1, [number2] ,…)`

The **SUM function** takes **3** types of inputs: **positive or negative numeric values**, **range**, and **cell references**. It takes these inputs and shows their summation as output. The first argument is mandatory, others are optional, and it takes up to **255 **numbers.

Here, we need to calculate the **Total Weight **of the **Products **in cell **D10 **by adding the individual weights of **Column D.**

Firstly, write the formula in the **D10 **cell like this.

`=SUM(SUBSTITUTE(D5:D9, " Kg", "")+0)`

Here, **D5:D9** refers to the **Weights **of **Products**.

Secondly, press **ENTER**.

Eventually, we’ll get the output as **82**.

**Formula Breakdown:**

**{=SUM(SUBSTITUTE(D9:D13, ” Kg”, “”)+0)}**

**SUM(SUBSTITUTE(“25 Kg, 10 Kg, 10 Kg, 7 Kg, 30, Kg”, ” Kg”, “”)+0)****SUM(“25, 10, 10, 7, 30” +0)**

**Kg**” with empty text.]

**SUM(25, 10, 10, 7, 30)**

**Kg**“, Excel still took it as text. By adding the

**Zero**(

**0**), the values were changed to numeric.]

**82**

**SUM function**added the values and returned the desired summation.]

**Read More:** **All the Easy Ways to Add up (Sum) a column in Excel**

**Similar Readings**

**How to Add Rows in Excel with Formula (5 ways)****[Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)****How to Sum Multiple Rows and Columns in Excel****Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)****How to Sum Selected Cells in Excel (4 Easy Methods)**

### 2. Utilizing LEFT and FIND Functions

The formula that we used here uses four Excel functions, the **FIND **function, the **LEFT** function, the **VALUE** function, and the **SUM** function. Firstly, the formula separates the numeric values and secondly adds them up.

Eventually, the syntax of the **FIND **function is as follows:

This function is used to find a certain text string within a text string.

`=FIND (find_text, within_text, [start_num])`

**find_text:** The text which is to find.**within_text: **The text which is to search within.**start_num **[Optional]: The starting position of the search. By default, **start_num=1**. The first character in **within_text** is character number 1.

You can learn about this function in detail by reading this **documentation** from Microsoft.

However, the syntax of the **LEFT **function is as follows:

This function is used to extract characters from text.

`=LEFT (text, [num_chars])`

**text**: The text string to extract the characters from.**num_chars **[Optional]: The number of characters to extract. It starts from the left. By default, **num_chars=1**.

The syntax of the **VALUE **function is as follows:

This function converts texts into numeric values.

`=VALUE (text)`

*text: **The text which is to convert into numbers.*Additionally, you can check out the first method’s the

**SUM**function section for understanding the function. It is explained there.

Here, we also need to calculate the

**Total Weight**of the

**Products**in cell

**D10**by adding the individual weights of

**column D**.

Firstly, write the formula in the **D10 **cell like this.

`=SUM(VALUE(LEFT(D5:D9,FIND(" ",D5:D9)-1)))`

Secondly, press **ENTER** and get the output as **82**.

**Formula Breakdown:**

**{=SUM(VALUE(LEFT(D9:D13,FIND(” “,D9:D13)-1)))}**

**SUM(VALUE(LEFT(D9:D13,FIND(” “,”25 Kg, 10 Kg, 10 Kg, 7 Kg, 30Kg”)-1)))****SUM(VALUE(LEFT(D9:D13,(3, 3, 3, 2, 3)-1)))**

**FIND function**returned the positions of the White Spaces in each cell.]

**SUM(VALUE(LEFT(D9:D13,(2, 2, 2, 1, 1))))**

**SUM(VALUE(LEFT(“25 Kg, 10 Kg, 10 Kg, 7 Kg, 30Kg”,(2, 2, 2, 1, 1))))****SUM(VALUE(25, 10, 10, 7, 30))**

**LEFT function**extracted the numeric characters from the text using the digit numbers of each cell.]

**SUM(25, 10, 10, 7, 30)**

**VALUE function**converted the numeric characters into numeric values.]

**82**

**SUM function**added the values and returned the desired summation.]

**Read More:** **How to Add Numbers in Excel (2 Easy Ways)**

### 3. Applying Combined Formula

We can sum cells with text and numbers using a combination of different functions. We can apply the combination of **SUM**, **IF**, **ISNUMBER**, **FIND, **and **LEFT **functions. Suppose we have the dataset below which has column headers as **File Number** and **Size **in **Columns B** and **C** respectively. The sizes of the files are in **3** different scales **KB**, **MB**, and **GB**. We need to calculate the total of **KB**, **MB, **and **GB **in **Column F** with the column header of **Total**.

Firstly, write the following formula in the **F5 **cell like this.

`=SUM(IF(ISNUMBER(FIND(E5,$C$5:$C$15)),`

** VALUE(LEFT($C$5:$C$15,FIND(E5,$C$5:$C$15)-1)),0)**)

Here, **C5:C15** refers to the cells of column header **Size **and **E5 **refers to **KB **in the column header **Unit**.

Secondly, press **ENTER**.

Thirdly, use the **Fill Handle** by dragging down the cursor while holding the **right-bottom** corner of the **F5 **cell.

Eventually, we will get the total of **KB**, **MB, **and **GB **as outputs.

## How to SUM Cells with Text in Excel

We can sum cells with text very easily in Excel. We can use **the COUNTIF function** in this case to calculate easily. Suppose, we have the following dataset with column headers as **Company Name** and **Branch**. We need to find out the sum of common branches in **Column F** with the column header as **Count**.

Initially, write the following formula in the **F5 **cell like this.

`=COUNTIF($C$5:$C$15,E5)`

Here, **C5:C15** refers to the cells with column headers as **Branch **and **E5 **refer to **USA**.

Secondly, press **ENTER**

Lastly, use the **Fill Handle** to get the output of the **Count **of different branches.

## Conclusion

That’s all about today’s session. And these are the ways to sum cells with text and numbers in Excel. We strongly believe this article would be highly beneficial for you. Don’t forget to share your thoughts and queries in the comments section and explore our website **ExcelDemy**, a one-stop Excel solution provider.

## Further Readings

**How to Sum Only Visible Cells in Excel (4 Quick Ways)****Sum If a Cell Contains Text in Excel (6 Suitable Formulas)****How to Sum Only Positive Numbers in Excel (4 Simple Ways)****Sum Values by Day in Excel (6 Methods)****How to Sum Between Two Numbers Formula in Excel****Sum If Cell Contains Specific Text in Excel (6 Ways)**