Summing numeric parts of alphanumeric values

Is the numeric part always at the end as per your example?

How many letters might there be, 1 2 .....or more?

Are there many different letters or only a few?

Hi JBeaucaire,

I said forbidden because my workbook is referenced to by other files and I don't want to screw up the existing format. I have considered using VBA too but I am not proficient enough to make it update the sum whenever the data in the range changes. Plus I want this to happen to several worksheets within this workbook, the scripting can be quite nasty.

Hi Daddylonglegs,

I am happy if you can solve this for the case where the values within the range is either numeric or take the form "a"+number (eg. 30, a50, a80).

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 Daddylonglegs,

Thanks for your prompt assistance. Your formula works well. But I am stumped when trying to incorporate this with a SUMIF function. I mean my range is a row under different column headings. I wish to sum those cells with column heading begining with "Actual". Normally without the alphanumeric issue, I would use something like:

=SUMIF($A$1:$AB$1, "Actual*",A2:AB2)

Any suggestion?

You can do that within SUMPRODUCT, so for the first formula I posted try

=SUMPRODUCT(SUBSTITUTE(0&UPPER(A2:AB2),"A","")*(LEFT(A1:AB1,6)="Actual"))

Hi Daddylonglegs, This works perfectly. You are a legend. Thanks for the trick.

I can use the below if my cells are either blank or contain a text 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

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

Hello aclx2s,

Please can you start a new thread with your question. If you think this thread is relevant then please quote the formulas and/or link back to this thread, thanks