How to count SPECIFIC text characters with a range of cells

dandeisel

New Member
Joined
Feb 1, 2017
Messages
16
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!
 

Some videos you may like

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
Joined
Oct 20, 2009
Messages
7,902
Office Version
365
Platform
Windows
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
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You could use:

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

dandeisel

New Member
Joined
Feb 1, 2017
Messages
16
Rory. Thank you very Much! That works.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Glad to help. :)
 

dandeisel

New Member
Joined
Feb 1, 2017
Messages
16
Steve I appreciate you taking the time to do what you did. When I first looked at it. I was like :confused:. LOL
 

Watch MrExcel Video

Forum statistics

Threads
1,089,886
Messages
5,410,986
Members
403,336
Latest member
amreeves87

This Week's Hot Topics

Top