summing numeric parts of alphanumeric strings

To Whom it May Concern,
I need to sum the numeric portions of any cell containing a certain letter within a row. I found a solution that works if all the cells within my row are either blank or contain a string with the "desired letter" lets say the letter is "a" so that we can compare it to the solution form the prior thread.

Link to prior thread: http://www.excelforum.com/excel-gene...ic-values.html

Here is an example of what entries may be in a row (always this format)

4a, x, 3p, 4c, x, x, 2p, 8a (values of numeric portion of alphanumeric string summed up in another column and equal 16 in this example. The application is for a schedule with hours worked and billed to either admin (a), comp time (c) or personal time (p)

Certain letters need to have a numeric values in front of them, others will always equal a constant (in my example x =12). I should mention their may also be cell entries with only a number in them (eg "8"

I can use the below if my cells are either blank or contain aan alphanumeric string with an "a". Is there a way to adjust this so that the numeric portions of only the cells with an a in them get summed.

If I use the below and my range contains any letter other than "a" I get an error I would like cells with other letters to be somehow ignored

IE my column may contain 8a, 4a and the summing works great but if my column contains 8a, 4a, m I get an error

Here is the formula from the prior post with solution by "daddylonglegs" (I am actually using rows not columns)

Originally Posted by daddylonglegs
If the letter is always "A" then you could use this formula

=SUMPRODUCT(SUBSTITUTE(0&UPPER(A1:A100),"A","")+0)

or for any letters but only either a single one at the start or none at all

=SUMPRODUCT((0&MID(A1:A100,1+ISERR(LEFT(A1:A100)+0),10))+0)

Where data is in the range A1:A100

both formulas will also allow blanks in the range

Hi ACL,

Please post a sample workbook with some data as you would have it in your live sheet, and also show the expected results. This will likely help you get a resolution more quickly.

If you're saying you want simply to amend the original to include only #a values then

Please Login or Register  to view this content.

If you want to add groups together you could use an ISNUMBER(MATCH(....)) test to the Sumproduct, eg suppose B1 contains string: a,c

Please Login or Register  to view this content.

The first formula in my prior post does as your request, you merely need to modify references to $A$1:$A$10 to be $B6:$AP6
assuming you will always use #a convention whenever a is used (ie a alone will no longer exist)

For your total formula in col AR this:

Please Login or Register  to view this content.

can be shortened somewhat given use of constants to:

Please Login or Register  to view this content.

you can apply similar logic to other columns as nec.