Assuming it is only 1 letter in front, try:
=SUMPRODUCT(--MID(A1:F1,2,10))
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.
Assuming it is only 1 letter in front, try:
=SUMPRODUCT(--MID(A1:F1,2,10))
See attached:
Is there anything different than yours?
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?
Last edited by Ash-uk; 2013-06-07 at 09:27 PM. Reason: Adding additional info
Ok then try:
=SUMPRODUCT(--(0&MID(A1:H1,2,10)))
where A1:H1 is the full range.
You must think I'm Dumb ... I did try but given me zero.
I attached the workbook
Your sample is not attached....
Here is mine with updated formula....
Hi I manage to used the formula but would not add the cell with numbers only !!
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)
Last edited by NBVC; 2013-06-07 at 09:53 PM.
Bookmarks