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

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
Asked:
Shaye Larsen
1 Solution
 
Phillip BurtonDirector, 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
 
Ejgil HedegaardCommented:
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now