SumIf question

[infuse]

HI Experts:

Thanks in advance for the help!!
I have three columns A, B and C. I want to compare numerical part of A (entry of A is combination of numeric and text) and if that PART is equal to the entry in B sum C corresponding to that. Example

Column A--------Column B---------Column C

21365ac -----------3215---------------- 56
654ewf--------------654 -----------------10
21354d-------------21354----------------12
2dfgtfjgh--------------2-------------------4

In the above example, second row, Column A and Column B has the same numeric part, 654. So I take column C = 10 for addition. Now row three, Column A and B has same numeric part that is 21354 so I add column C which is 12 to the previous 10. I think you guys got the point.

Pleas don’t give the formula; please give me the functions that I can use to get their. I am struggling with separating the numerical part, since it is not of same length. If anybody can help with that part I know how to do the rest. If VBA is the option then I think I can manage. But what I would like to know is, are there any way to do this with formulas?

Thanks
Infuse

 

[sam slade]

Hi infuse,

personally i would strip out the numbers into a seperate column using vba. I cannot think of a function that would recognise the numeric element of a cell.

Sam

 

[infuse]

Thanks Sam Slade, I will wait for some more time , incase some one else come up with a formula.
Thanks once again
Infuse

 

[Domenic]

To separate numbers from alpha-characters, assuming that the formatting for Column A is consistent, that is, numbers always precede letters, try...

=LEFT(A2,LOOKUP(10,--MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)))))+0

Hope this helps!

 

[mortgageman]

See if this helps. BTW, what does your post have to do with sumif?

Gene, "The Mortgage Man", Klein

 

[mhenk]

Here's a formula that I think works. It's an array formula, so enter it with Control, Shift & Enter:

=SUM((VALUE(LEFT(A1:A4,LEN(B1:B4)))=B1:B4)*C1:C4)

 

[mhenk]

NM, my formula does not work if, for example, column b has 2, and column a has 243age. oops.

 

[infuse]

Wowwwwwwwwwwwwwwwwwwwwwwwwwww you guys are the gods

Mhenk : Your formula works exactly the way I wanted. If column B has 2 and column A has 243age, I DON’T want to sum the C column. I want to sum when there is an EXACT match between A and B column. Your formula does exactly this. I love the logic that you used ( that is the “len” function to come up with the length)
I have a question, I looked at your formula and I understood everything, but I don’t know how the Value function works. I know it is there to check the criteria that if B column Matches A column but I don’t know how does it work. It would be great if you can explain this.

Mortgageman: Sorry for the title of the thread. I thought that is function that I have to use to solve this problem

Domenic: Your solution does work. But you can see that Mhenk’s solution is better.

Thanks once again for all the help

 

[Domenic]

If column B has 2 and column A has 243age, I DON’T want to sum the C column. I want to sum when there is an EXACT match between A and B column.

As mhenk has already pointed out, his formula will not return the desired result in this circumstance.