SumIf question

infuse

Board Regular
Joined
Dec 7, 2005
Messages
126
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

sam slade

New Member
Joined
Sep 14, 2006
Messages
22
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

Board Regular
Joined
Dec 7, 2005
Messages
126
Thanks Sam Slade, I will wait for some more time , incase some one else come up with a formula.
Thanks once again
Infuse
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,309
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

Well-known Member
Joined
Jun 30, 2005
Messages
2,015

ADVERTISEMENT

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

Gene, "The Mortgage Man", Klein
 

mhenk

Well-known Member
Joined
Jun 13, 2005
Messages
589
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

Well-known Member
Joined
Jun 13, 2005
Messages
589

ADVERTISEMENT

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

infuse

Board Regular
Joined
Dec 7, 2005
Messages
126
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,309
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,488
Messages
5,529,164
Members
409,852
Latest member
Perry123
Top