newbie: count total of number in strings

Assumed that I have a four columns as follow:
a1 b1 c1 d1

6a 3a 7a

All I want is to count the total of cell:a1,b1,c1 and place the total number in d1 cells. I put the formula =SUMIF(a1:c1,"*a",a1:c1) in the cell d1, but it don't work.
Now I'm really need you help to solve above problem.
Please help me.
many thanks.

Use this formula if "A" will always be the only & last character in the cell.

=SUMPRODUCT(--(RIGHT(A1:C1,1)="A"),INT(LEFT(A1:C1,LEN(A1:C1)-1)))

Cheers!

The above formula will return #VALUE! if one of the cells in the range does not have more than 2 characters.

Try

=SUMPRODUCT(--(RIGHT(A1:C1,1)="A"),IF(LEN(A1:C1)<=1,0,INT(LEFT(A1:C1,LEN(A1:C1)-1))))

and on exit press CTRL+SHIFT+ENTER to give

{=SUMPRODUCT(--(RIGHT(A1:C1,1)="A"),IF(LEN(A1:C1)<=1,0,INT(LEFT(A1:C1,LEN(A1:C1)-1))))}

Cheers!

thank all you for the quick reply.
I'll try it now

thank you very much.
I asked myself when I could input formula into cells like you.
All you are excel expert.
thanks again

Help me! Help me
Once again, thank you for your reply and sorry for my above unclear post.
Your formula works quite well but only if there are only numbers 2a, 4a, 5a, 8a etc.
It don't count total exactly in the case that the value is 2.3a 5 1.5 a 4 7 0.5a etc
I really need your help on this issue. thank you in advanced.

could anyone help me, please?
I really need your help on this problem.
thanks

Coud anyone help me please?
thanks

type sums("a1:c1") at cell d1

use this function


Function SUMS(R As Range)
Dim s As Double


For Each c In R
s = s + (Mid(c.Value, 1, Len(c.Value) - 1))
Next
SUMS = s

End Function