Count Or Sum Specified Number In a Single Cell

Got any Excel/VBA Questions? Free Excel Help.

Count or Sum Specified Number In Single Cell Housing Many Numbers

Function Count_Sum_NumbersInCell(rCell As Range, _

    sNumber As Single, strDelimeter, Optional bSum As Boolean) As Single

    Dim vArray

    Dim lLoop As Long

    Dim sResult As Single

       

    vArray = Split(rCell, strDelimeter)



    If bSum = False Then

        For lLoop = 0 To UBound(vArray)

                If vArray(lLoop) = _

                    sNumber Then sResult = sResult + 1

        Next lLoop

    Else

        With WorksheetFunction

            For lLoop = 0 To UBound(vArray)

                    If vArray(lLoop) = _

                        sNumber Then sResult = .Sum(sResult, vArray(lLoop))

            Next lLoop

        End With

    End If



    Count_Sum_NumbersInCell = sResult

End Function

If A1 housed 1 3 30 3 23 3