Sum text and numbers

A

Ash-uk

New member
Joined
Nov 13, 2012
Messages
11
Reaction score
0
Points
0
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?
 

Attachments

  • Book2.xlsx
    8.4 KB · Views: 13
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?
 

Attachments

  • Book2.xlsx
    10.9 KB · Views: 7
Last edited:
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....
 

Attachments

  • Book2.xlsx
    8.4 KB · Views: 11
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:
here is my workbook see if you can help.
 

Attachments

  • Formula.xlsx
    13.4 KB · Views: 10
As mentioned in previous post:

=SUMPRODUCT(--(0&MID(F12:Y12,2,10)))+SUM(F12:Y12) if single-digit

or

=SUMPRODUCT(--ISERR(F12:Y12+0),--(0&MID(F12:Y12,2,10)))+SUM(F12:Y12) if multi-digit
 
Its works perfect.

Thank you so much ......:cool2:
 
Is there any other way to refesh so that when inputting data the sum update automatically ?
 
It did auto refresh on the sample you attached... however, go to Formulas tab, then in the Calculation Options drop down, select Automatic (if not already selected)
 
NBVC said:
It did auto refresh on the sample you attached... however, go to Formulas tab, then in the Calculation Options drop down, select Automatic (if not already selected)
Click to expand...

Thanks I realised it was on manual.

Thanks a lot for helping me.
 
You must log in or register to reply here.
Back
Top