<div>
It's possible to do it on one array formula. But it's much more auditable to separate it back out.<br />
<br />
In your example, the numbers are all two digit. So you can do<br />
<br />
=value(left(c1,2))<br />
<br />
And then Sumifs them.
</div>


It's possible to do it on one array formula. But it's much more auditable to separate it back out.

In your example, the numbers are all two digit. So you can do

=value(left(c1,2))

And then Sumifs them.

Sum-numbers-before-text.xlsx

<div>
<div class="content wysiwyg-content">
I have a vertical range of values. &nbsp;Each cell concatenates a number and a string. &nbsp; For example (=C2&amp;C3) &nbsp;where C2 = 30 and C3 = SomeText. &nbsp;The result value of the cell would be 30SomeText.<br />
<br />
I realize that the concatenate formula turns this into a string and it is no longer a number that cam be calculated. &nbsp;But I am wondering if there is some way around this. &nbsp;I want to look at the range and add all the numbers that have the same text.<br />
<br />
For example, if this was my range of cells.<br />
80SomeText2<br />
10SomeText<br />
30SomeText<br />
20SomeText2<br />
<br />
I want to look at that range, find the &quot;SomeText2&quot; values, and add the numbers which in this case would result in a 100.<br />
<br />
Any thoughts?
</div>
</div>


I have a vertical range of values.  Each cell concatenates a number and a string.   For example (=C2&C3)  where C2 = 30 and C3 = SomeText.  The result value of the cell would be 30SomeText.

I realize that the concatenate formula turns this into a string and it is no longer a number that cam be calculated.  But I am wondering if there is some way around this.  I want to look at the range and add all the numbers that have the same text.

For example, if this was my range of cells.
80SomeText2
10SomeText
30SomeText
20SomeText2

I want to look at that range, find the "SomeText2" values, and add the numbers which in this case would result in a 100.

Any thoughts?

Sum a range of cells which include concatenated values

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.