How to count SPECIFIC text characters with a range of cells

[dandeisel]

Hey guys,

Can someone assist me in writing a formula for counting how many "I" are in a range of cells. For example I have a range of cells B11:AF12 that have different letters in it to identify the kind of Lead Source we receive. "I" represents Internet. I need to find out how many I's are in that range of cells. Keep in mind there could be multiple I's in a single cell.

Thanks guys!

 

[steve the fish]

Try this:

Dim arr, i As Long, j As Long, L1 As Long, L2 As Long

arr = Range("B11:AF12")


For i = LBound(arr, 1) To UBound(arr, 1)
    For j = LBound(arr, 2) To UBound(arr, 2)
        L1 = L1 + Len(arr(i, j))
        arr(i, j) = Replace(LCase(arr(i, j)), "i", "")
        L2 = L2 + Len(arr(i, j))
    Next
Next


MsgBox "Number of character I in the range is " & L1 - L2

 

[RoryA]

You could use:

=SUMPRODUCT(LEN(B11:AF12)-LEN(SUBSTITUTE(B11:AF12,"I","")))

 

[steve the fish]

Really i should learn to read...

 

[dandeisel]

Rory. Thank you very Much! That works.

 

[RoryA]

Glad to help.

 

[dandeisel]

Steve I appreciate you taking the time to do what you did. When I first looked at it. I was like . LOL