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.
Watch Video – Sum Cells with Text and Numbers in Excel
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)
- SUM(25, 10, 10, 7, 30)
- 82
Read More: All the Easy Ways to Add up (Sum) a column in Excel
Similar Readings
- Add Rows in Excel with Formula (5 ways)
- How to Assign Value to Text and Sum in Excel (2 Easy Methods)
- [Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)
- Use SUMIF with Text in Excel (9 Easy Ways)
- How to Sum Multiple Rows and Columns in Excel
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)))
- 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))
- SUM(25, 10, 10, 7, 30)
- 82
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.
Read More: How to Sum Selected Cells in Excel (4 Easy Methods)
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.
Read More: How to Sum Text Values Like Numbers in Excel (3 Methods)
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.
Related Articles
- Sum Only Visible Cells in Excel (4 Quick Ways)
- How to Sum If Cell Contains Text in Another Cell in Excel
- Sum If a Cell Contains Text in Excel (6 Suitable Formulas)
- How to Sum Names in Excel (4 Suitable Ways)
- Sum Only Positive Numbers in Excel (4 Simple Ways)
- How to Sum Only Numbers and Ignore Text in Same Cell in Excel
- Sum Values by Day in Excel (6 Methods)
in CELL A1 value pass as text 3+4+5+6+7 at Cell A2 sum value which is 25 should be shown.
Thank you for your very useful suggestion, VAIBHAV SRIVASTAVA. I understand you suggested summing a text string which is much more relatable to this article. And to do so, you can merge the SUM, VALUE, and TEXTSPLIT functions. Here is the combined formula:
=SUM(VALUE(TEXTSPLIT(A1,"+")))Don’t hesitate if you have further suggestions for us. Thanks again.
Regards,
Yousuf Khan Shovon