newbie: count total of number in strings

cuongvt · #1 (**permalink**) 01-13-05, 00:53

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.

DavidCoutts · #2 (**permalink**) 01-13-05, 07:32

This might be total Overkill but try this

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

HTH
Dave

cuongvt · #3 (**permalink**) 01-13-05, 21:46

thanks, it works

cuongvt · #4 (**permalink**) 01-14-05, 21:43

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.

DavidCoutts · #5 (**permalink**) 01-17-05, 04:10

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

Internet Services

Web Development

Digital Marketing

Consumer Tech