# Thread: Sum text and numbers

1. ## Sum text and numbers

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.  Reply With Quote

2. Assuming it is only 1 letter in front, try:

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

3. Originally Posted by NBVC Assuming it is only 1 letter in front, try:

=SUMPRODUCT(--MID(A1:F1,2,10))
Hi Thanks
But I'm still getting #VALUE!  Reply With Quote

4. See attached:

Is there anything different than yours?  Reply With Quote

5. 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?  Reply With Quote

6. Ok then try:

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

where A1:H1 is the full range.  Reply With Quote

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

8. Your sample is not attached....

Here is mine with updated formula....  Reply With Quote

9. Hi I manage to used the formula but would not add the cell with numbers only !!  Reply With Quote

10. 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)  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•