# How to count SPECIFIC text characters with a range of cells

#### dandeisel

##### New Member
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!

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### steve the fish

##### Well-known Member
Try this:

Code:
``````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

##### MrExcel MVP, Moderator
You could use:

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

#### steve the fish

##### Well-known Member
Really i should learn to read...

#### dandeisel

##### New Member
Rory. Thank you very Much! That works.

#### dandeisel

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