Sum text and numbers

[Ash-uk]

Hi,

I am trying to sum a set of row that contains Numbers and Texts. Can you help please.

Example:
A B C D E F
M5 M2 M6 P2 P1 B5

I just want colum A7 to A1:F1 sum M5+M2+M6+P2+P1+B5 = 21

I am unable to sum with the letter infront of the number.

[NBVC]

Assuming it is only 1 letter in front, try:

=SUMPRODUCT(--MID(A1:F1,2,10))

[Ash-uk]

Assuming it is only 1 letter in front, try:

=SUMPRODUCT(--MID(A1:F1,2,10))

Hi Thanks
But I'm still getting #VALUE!

[NBVC]

See attached:

Is there anything different than yours?

[Ash-uk]

Hi sorry I forget some of the cell in the same row are empty... probably that is why I'm getting the error message.
thanks a lot for your help. What should i do?

[NBVC]

Ok then try:

=SUMPRODUCT(--(0&MID(A1:H1,2,10)))

where A1:H1 is the full range.

[Ash-uk]

You must think I'm Dumb ... I did try but given me zero.
I attached the workbook

[NBVC]

Your sample is not attached....

Here is mine with updated formula....

[Ash-uk]

Hi I manage to used the formula but would not add the cell with numbers only !!

[NBVC]

You keep changing the sampleset.....

Try:

=SUMPRODUCT(--(0&MID(A1:H1,2,10)))+SUM(A1:H1)

with same assumption of A1:H1 being your dataset.

that's also assuming single digits cells....

If multi-digit is possible... then:

=SUMPRODUCT(--ISERR(A1:H1+0),--(0&MID(A1:H1,2,10)))+SUM(A1:H1)