Excel Count Functions

[Gasbag]

I am trying to count the number of cells in a row that contain one or more of
three letters?

 

[Gary Mc]

I'm sure there are other and probably better solutions but this will work
=SUM(COUNTIF(B2:E2,"*a*"),COUNTIF(B2:E2,"*x*"))
Hope this is helpful
GMc

 

[Gary Mc]

My bad, you are absolutely correct. I apologize for the error!

 

[Rick Rothstein]

Give this array-entered** formula a try...

=SUM(IF(ISNUMBER(SEARCH("a",A1:A100)),1,IF(ISNUMBER(SEARCH("b",A1:A100)),1,IF(ISNUMBER(SEARCH("c",A1:A100)),1,0))))

**Commit this formula using Ctrl+Shift+Enter, not just Enter by itself.

Note: Change the "a", "b", and "c" to the letters you want to find (keep the
letters in quotes when you do).

 

[Mike H]

Someone must be able to do better than this

call with

=CountChar(B2:E2,"a","b","c")

Function CountChar(rng As Range, ch1 As String, ch2 As String, ch3 As
String) As Long
For Each c In rng
If InStr(UCase(c.Value), UCase(ch1)) Then CountChar = CountChar + 1
If InStr(UCase(c.Value), UCase(ch2)) Then CountChar = CountChar + 1
If InStr(UCase(c.Value), UCase(ch3)) Then CountChar = CountChar + 1
Next
End Function

Mike

 

[Rick Rothstein]

Of course, you said "in a row". Try this array-entered formula instead...

=SUM(IF(ISNUMBER(SEARCH("a",1:1)),1,IF(ISNUMBER(SEARCH("b",1:1)),1,IF(ISNUMBER(SEARCH("c"1:1)),1,0))))

**Commit this formula using Ctrl+Shift+Enter, not just Enter by itself.

The 1:1 in each part of the formula means Row 1... change them all (there
are 3 of them) to the row you are interested in (for example 4:4 for Row 4).
And, of course, still change the individual "a", "b", and "c" letters to the
letters you want to look for.

 

[Rick Rothstein]

You can simplify your function like this...

Function CountChr(Rng As Range, C1 As String, _
C2 As String, C3 As String) As Long
Dim C As Range
For Each C In Rng
If C.Value Like "*[" & C1 & C2 & C3 & "]*" Then CountChr = CountChr + 1
Next
End Function

Note I shortened the name of your function and some of your argument names
in order to prevent newsreaders from word-wrapping the long If-Then
statement.

 

[Mike H]

Thanks rick,

I continually miss the option of putting values in an array and using Like.

Mike

You can simplify your function like this...

Function CountChr(Rng As Range, C1 As String, _
C2 As String, C3 As String) As Long
Dim C As Range
For Each C In Rng
If C.Value Like "*[" & C1 & C2 & C3 & "]*" Then CountChr = CountChr + 1
Next
End Function

Note I shortened the name of your function and some of your argument names
in order to prevent newsreaders from word-wrapping the long If-Then
statement.

--
Rick (MVP - Excel)

Someone must be able to do better than this

call with

=CountChar(B2:E2,"a","b","c")

Function CountChar(rng As Range, ch1 As String, ch2 As String, ch3 As
String) As Long
For Each c In rng
If InStr(UCase(c.Value), UCase(ch1)) Then CountChar = CountChar + 1
If InStr(UCase(c.Value), UCase(ch2)) Then CountChar = CountChar + 1
If InStr(UCase(c.Value), UCase(ch3)) Then CountChar = CountChar + 1
Next
End Function

Mike

 

[Rick Rothstein]

I forgot the upper/lower case stuff. Here is a more general approach that
will allow the user to look for more (or less) than three characters and
which takes care of the letter casing problem as well...

Function CountChr(Rng As Range, ParamArray Char()) As Long
Dim X As Long
Dim C As Range
Dim Chars As String
For X = LBound(Char) To UBound(Char)
Chars = Chars & UCase(Char(X))
Next
For Each C In Rng
If UCase(C.Value) Like "*[" & Chars & "]*" Then CountChar = CountChar +
1
Next
End Function

Now the user can do this...

=CountChr(A1:Z1,"a","b")

or this...

=CountChr(A1:Z1,"a","b","c","d","e")

etc.

--
Rick (MVP - Excel)

You can simplify your function like this...

Function CountChr(Rng As Range, C1 As String, _
C2 As String, C3 As String) As Long
Dim C As Range
For Each C In Rng
If C.Value Like "*[" & C1 & C2 & C3 & "]*" Then CountChr = CountChr + 1
Next
End Function

Note I shortened the name of your function and some of your argument names
in order to prevent newsreaders from word-wrapping the long If-Then
statement.

--
Rick (MVP - Excel)

Someone must be able to do better than this

call with

=CountChar(B2:E2,"a","b","c")

Function CountChar(rng As Range, ch1 As String, ch2 As String, ch3 As
String) As Long
For Each c In rng
If InStr(UCase(c.Value), UCase(ch1)) Then CountChar = CountChar + 1
If InStr(UCase(c.Value), UCase(ch2)) Then CountChar = CountChar + 1
If InStr(UCase(c.Value), UCase(ch3)) Then CountChar = CountChar + 1
Next
End Function

Mike

 

[Lars-Åke Aspelin]

I am trying to count the number of cells in a row that contain one or more of
three letters?

Here is an alternative to already presented solution.
This one is not an array formula:

=SUMPRODUCT(1-ISERROR(FIND("a",A1:J1))*ISERROR(FIND("b",A1:J1))*ISERROR(FIND("c",A1:J1)))

a,b,c to be replaced with your specific letters.
A1:J1 to be adjusted to cover you width of your data row.
Note that FIND is case sensitive so if you want to distinguish letter
"a" from letter "A" in your data FIND it is a better choice than
SEARCH which is not case sensitive.

Hope this helps / Lars-Åke

 

[Harlan Grove]

I am trying to count the number of cells in a row that contain one or more of
three letters?

Not particularly general, but the following may be the shortest
formula that would do this.

=COUNT(1/(MMULT({1,1,1},--(SUBSTITUTE(A11,{"a";"g";"m"},"")=A11))
<3))

This doesn't have to be entered as an array formula. You could make it
more general. Define a name (I'll use X) referring to either a
constant array of the letters sought or to a 1-column by multiple row
range and use an array formula like

=COUNT(1/(MMULT(TRANSPOSE(CODE(X)^0),--(SUBSTITUTE(A11,X,"")=A11))
<COUNTA(X)))

for a more general approach.

 

[Harlan Grove]

You can simplify your function like this...

Function CountChr(Rng As Range, C1 As String, _
                   C2 As String, C3 As String) As Long
  Dim C As Range
  For Each C In Rng
    If C.Value Like "*[" & C1 & C2 & C3 & "]*" Then CountChr = CountChr + 1
  Next
End Function

....

Or make it general. Like

Function foo(a As Variant, p As String) As Double
Dim x As Variant
If Not TypeOf a Is Range And Not IsArray(a) Then a = Array(a)
For Each x In a
If x Like p Then foo = foo + 1
Next x
End Function

which could be used in formulas like

=foo(A11,"*[agm]*")

This would allow counting any valid LIKE pattern.

 

[Rick Rothstein]

As written, your formula is case-sensitive. If the OP requires a
case-insensitive solution, perhaps this modification will do...

=COUNT(1/(MMULT({1,1,1},--(SUBSTITUTE(LOWER(A11),{"a";"g";"m"},"")=A11))<3))

 

[Rick Rothstein]

I like this idea better than my paramarray suggestion; however, I would
change your function slightly so the user would not have to know the syntax
of the Like operator...

Function Foo(A As Variant, P As String) As Double
Dim X As Variant
If Not TypeOf A Is Range And Not IsArray(A) Then A = Array(A)
For Each X In A
If X Like "*[" & P & "]*" Then Foo = Foo + 1
Next X
End Function

With this variation, all the user has to do is call the function like
this...

=Foo(A11,"agm")

--
Rick (MVP - Excel)

You can simplify your function like this...

Function CountChr(Rng As Range, C1 As String, _
C2 As String, C3 As String) As Long
Dim C As Range
For Each C In Rng
If C.Value Like "*[" & C1 & C2 & C3 & "]*" Then CountChr = CountChr + 1
Next
End Function

....

Or make it general. Like

Function foo(a As Variant, p As String) As Double
Dim x As Variant
If Not TypeOf a Is Range And Not IsArray(a) Then a = Array(a)
For Each x In a
If x Like p Then foo = foo + 1
Next x
End Function

which could be used in formulas like

=foo(A11,"*[agm]*")

This would allow counting any valid LIKE pattern.

 

[Rick Rothstein]

I just noticed that I forgot to provide for case insensitivity. How about we
make that optional (and change the function and arguments to something more
meaningful)...

Function Foo(A As Variant, ByVal P As String, _
Optional C As Boolean) As Double
Dim X As Variant
If Not TypeOf A Is Range And Not IsArray(A) Then A = Array(A)
If C Then P = LCase(P) & UCase(P)
For Each X In A
If X Like "*[" & P & "]*" Then Foo = Foo + 1
Next X
End Function

As set up, the function is case sensitive; if the user wants the count to be
case insensitive, they just need to include True for the 3rd argument. For
example...

=Foo(A11,"agm",TRUE)

--
Rick (MVP - Excel)

I like this idea better than my paramarray suggestion; however, I would
change your function slightly so the user would not have to know the syntax
of the Like operator...

Function Foo(A As Variant, P As String) As Double
Dim X As Variant
If Not TypeOf A Is Range And Not IsArray(A) Then A = Array(A)
For Each X In A
If X Like "*[" & P & "]*" Then Foo = Foo + 1
Next X
End Function

With this variation, all the user has to do is call the function like
this...

=Foo(A11,"agm")

--
Rick (MVP - Excel)

You can simplify your function like this...

Function CountChr(Rng As Range, C1 As String, _
C2 As String, C3 As String) As Long
Dim C As Range
For Each C In Rng
If C.Value Like "*[" & C1 & C2 & C3 & "]*" Then CountChr = CountChr + 1
Next
End Function

...

Or make it general. Like

Function foo(a As Variant, p As String) As Double
Dim x As Variant
If Not TypeOf a Is Range And Not IsArray(a) Then a = Array(a)
For Each x In a
If x Like p Then foo = foo + 1
Next x
End Function

which could be used in formulas like

=foo(A11,"*[agm]*")

This would allow counting any valid LIKE pattern.

 

[Ashish Mathur]

Hi,

You can also try something like this. In a separate range , day D1012,
type the 3 letters. Now suppose your entries are in A1:A50. in cell B1,
type this array formula (Ctrl+Shift+Enter) and copy down

=1*OR(ISNUMBER(SEARCH($D$10:$D$12,A1,1)))

Now simply sum up column B.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

It also counts cells that are numeric:

gmail...10...AAA...<empty>...3M

If there might be both text and numbers then you need to add a test for
text:

=SUMPRODUCT(--(MMULT({1,1,1},--(SUBSTITUTE(LOWER(A11),{"a";"g";"m"},"")=A11))<3),--(ISTEXT(A11)))

If there are errors in the range that'll return the error and the COUNT
version(s) will return 0.

gmail...10...AAA...#N/A...3M

 

[Harlan Grove]

I like this idea better than my paramarray suggestion; however, I would
change your function slightly so the user would not have to know the syntax
of the Like operator...

....

IMO, better for users to know the syntax for LIKE operator patterns
since that would allow significantly greater flexibility and
functionality than simply searching for alternative single characters.

 

[Harlan Grove]

It also counts cells that are numeric:

gmail...10...AAA...<empty>...3M

If there might be both text and numbers then you need to add a test for
text:

=SUMPRODUCT(--(MMULT({1,1,1},--(SUBSTITUTE(LOWER(A11),
{"a";"g";"m"},"")=A11))<3),--(ISTEXT(A11)))

....

No, better to use ISNUMBER(SEARCH(...)).

=COUNT(1/MMULT({1,1,1},--ISNUMBER(SEARCH({"a";"g";"m"},A11))))

 

[Rick Rothstein]

True, but Like is a VB programming concept and my guess is that most Excel
users are not VB literate, so I would think many (if not most) of the users
would not be familiar with Like's operator patterns. However, I think we can
cater to both types of users with something like this (off the top of the
head code) maybe...

Function Foo(A As Variant, ByVal P As String, _
Optional C As Boolean) As Double
Dim X As Variant
If Not P Like "*[*?#[]*" Then P = "*[" & P & "]*"
If Not TypeOf A Is Range And Not IsArray(A) Then A = Array(A)
For Each X In A
If C Then
If UCase(X) Like UCase(P) Then Foo = Foo + 1
ElseIf X Like P Then
Foo = Foo + 1
End If
Next X
End Function

If the user uses an asterisk, question mark, hash mark or left square
bracket in his/her pattern string, then the code assumes the user has
constructed a Like pattern string and passes it through as it; if none of
those characters are present, then the code assumes the user is looking for
a simple letter match. The optional C argument still optionally allows for
case sensitive or case insensitive searches.