# SumIf question

#### infuse

##### Board Regular
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

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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

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

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!

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

Gene, "The Mortgage Man", Klein

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)

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

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

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.

Replies
9
Views
371
Replies
0
Views
412
Replies
1
Views
267
Replies
15
Views
395
Replies
1
Views
496

1,219,690
Messages
6,149,742
Members
450,911
Latest member
williamsisland

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back