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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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,721
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
591
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
591

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,721
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.
 

Forum statistics

Threads
1,141,139
Messages
5,704,514
Members
421,353
Latest member
jekoxien15

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top