Sum Cells Text Numbers within same Cell Microsoft Excel

Add Numbers Text of multiple cells Excel 2016

excel sum cells text numbers | sum if cell contains number | excel if cell contains text then

Previous Post: SumIf function of Formulas tab in Microsoft Excel 2016

This post guides us on about Sum Cells Text Numbers.

The prior post, illustrates about the SumIf function. It is part of the Math Trig functions of the Formulas tab. This function helps users to add numbers on basis of the If condition. So, when the If condition becomes True, the numbers in the cells get add up.

Nevertheless, now in this post, we’ll try to know something special. Let us add sum numbers that contain text along them. In simple words, add only the numbers from the cells, those have both texts and numbers in them. Even though text is present, neglect them.

Sum Cells Text Numbers MS Excel 2016 – continued

So, in order to achieve this, we’ll require a nested Formula. This type of formula contains multiple others formulas as well as functions. Generally, nested formula can be quite long as well. Also, the same formula can nest itself, one inside another.

Alright, let us see an example and try to understand the following. How to Sum Cells Text Numbers within same Cell in Microsoft Excel. Suppose, we’ve some words along with some numbers in the same cell. They are Conference 15, Transfer 35, Software 12, Conference 45, Transfer 20, from cell range A1 to A5 i.e A1:A5.

Sum Cells Text Numbers Microsoft Excel 2016

And, also we’ve another table in which we need the answers. In this table, we’ve the words Conference, and Transfer in the first and second cell. So, now must use the following formula to add the numbers.

Formula to Sum Cells Text Numbers: 

{=SUM(IF(ISNUMBER(FIND(C1,$A$1:$A$5)),VALUE(RIGHT($A$1:$A$5,
FIND(C1,$A$1:$A$5)+1)),0))}

In the above formula, there are a number of functions. First of all, the SUM function, next is the IF function. Then is the ISNUMBER function. Further next is the FIND function.

Even more is the VALUE function, Moreover is the Right function, and finally is the FIND function once more. But, notice here, it’s an array formula, so we’ll have to press the Ctrl+Shift+Enter keys all together.

In an Array Formula, these three keys are used to get the final results. After using the above mentioned formula, we’ll get our results. Lastly, for Conference, the addition of the numbers will be 60; and for Transfer 55 by dragging with the mouse respectively.

Tags: Microsoft Excel Tips