Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    133

    Red face 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.
    Please help me.
    many thanks.

  2. #2
    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. #3
    Join Date
    May 2004
    Posts
    133
    thanks, it works

  4. #4
    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. #5
    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
  •