• Status: Solved
• Priority: Medium
• Security: Public
• Views: 81

# Sum a range of cells which include concatenated values

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?
0
Shaye Larsen
1 Solution

Director, Practice Manager and Computing ConsultantCommented:
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.
0

Commented:
Here is an array formula for a variable number of digits.
Array formulas has to be entered with Ctrl+Shift+Enter.

Values in A2:A5.
Text to search for in C2.
=SUM(IF(RIGHT(\$A\$2:\$A\$5,LEN(C2))=C2,VALUE(LEFT(\$A\$2:\$A\$5,LEN(\$A\$2:\$A\$5)-LEN(C2))),0))

See sheet
Sum-numbers-before-text.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.