Please help conditional summing numeric portions of text entires

[Jac Flasschoen]

I am trying to solve an Excel 2007 function dilemma using 2-columns. One column consists of values; the second is text but the text consists of the first two characters being alpha and the remaining 4 digits are numbers. Therefore, the first column is labeled "amount" and the second is cost area identification. As mentioned before, this is always a 6-digit entry with the first two digits always being text; example AB5301, AB5381, etc. The text characters are always AB but the number portion changes but always 4 digits in length. The issue, sum the numeric portion of the cost area ID based on whether there is a corresponding numeric value next to the ABxxxx cost ID. I have tried SUMIFS and have conditioned the cost area ID by converting to numeric using value(right(A2:A15,4). Is this something you can assist me with? The following are the columns

Amount Cost ID
5.00 AB5301
125.00 AB5302
0 AB5303
200.00 AB5304

From this simplistic example, the sum of numeric portion of the cost area ID containing values greater than 0 is 21,210. I have attempted using the SUMIFS function but receive the VALUE! error message,

[pike]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST