# Thread: newbie: count total of number in strings

1. Registered User
Join Date
May 2004
Posts
133

## Unanswered: 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.
many thanks.

2. Registered User
Join Date
Jan 2004
Location
Aberdeen, Scotland
Posts
1,067
This might be total Overkill but try this

=SUMPRODUCT((RIGHT((A1:C1),1) = "a")*(LEFT((A1:C1),1)))

HTH
Dave

3. Registered User
Join Date
May 2004
Posts
133
thanks, it works

4. Registered User
Join Date
May 2004
Posts
133
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.

5. Registered User
Join Date
Jan 2004
Location
Aberdeen, Scotland
Posts
1,067
Ok ive written a small udf for you

Code:
```Function SumTail(ByVal rngSum As Range, strChar As String) As Double
Dim rng As Range

'loop through each cell and sum as appropriate
For Each rng In rngSum
'Check Condition
If Right\$(rng.Value, 1) = strChar Then
'add value if expression is Valid
SumTail = SumTail + CDbl(Left\$(rng.Value, Len(rng.Value) - Len(strChar)))
End If
Next rng
End Function```
This should do what you want

Dave

#### Posting Permissions

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