Excel Count Functions
 

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

Excel Count Functions
 

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

"Gasbag" wrote:

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

Excel Count Functions
 

Won't that double count if the cell contains both a and x?
--
David Biddulph

"Gary Mc" wrote in message
...
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

"Gasbag" wrote:

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

Excel Count Functions
 

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

"David Biddulph" wrote:

Won't that double count if the cell contains both a and x?
--
David Biddulph

"Gary Mc" wrote in message
...
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

"Gasbag" wrote:

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

Excel Count Functions
 

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

=SUM(IF(ISNUMBER(SEARCH("a",A1:A100)),1,IF(ISNUMBE R(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).

--
Rick (MVP - Excel)


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

Excel Count Functions
 

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

"Gary Mc" wrote:

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

"David Biddulph" wrote:

Won't that double count if the cell contains both a and x?
--
David Biddulph

"Gary Mc" wrote in message
...
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

"Gasbag" wrote:

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

Excel Count Functions
 

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

=SUM(IF(ISNUMBER(SEARCH("a",1:1)),1,IF(ISNUMBER(SE ARCH("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 (MVP - Excel)


"Rick Rothstein" wrote in message
...
Give this array-entered** formula a try...

=SUM(IF(ISNUMBER(SEARCH("a",A1:A100)),1,IF(ISNUMBE R(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).

--
Rick (MVP - Excel)


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

Excel Count Functions
 

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)


"Mike H" wrote in message
...
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

"Gary Mc" wrote:

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

"David Biddulph" wrote:

Won't that double count if the cell contains both a and x?
--
David Biddulph

"Gary Mc" wrote in message
...
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

"Gasbag" wrote:

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

Excel Count Functions
 

Thanks rick,

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

Mike

"Rick Rothstein" wrote:

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)


"Mike H" wrote in message
...
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

"Gary Mc" wrote:

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

"David Biddulph" wrote:

Won't that double count if the cell contains both a and x?
--
David Biddulph

"Gary Mc" wrote in message
...
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

"Gasbag" wrote:

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

Excel Count Functions
 

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)


"Rick Rothstein" wrote in message
...
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)


"Mike H" wrote in message
...
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

"Gary Mc" wrote:

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

"David Biddulph" wrote:

Won't that double count if the cell contains both a and x?
--
David Biddulph

"Gary Mc" wrote in message
...
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

"Gasbag" wrote:

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

Excel Count Functions
 

On Sun, 7 Dec 2008 05:00:05 -0800, Gasbag
wrote:

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

Excel Count Functions
 

Gasbag wrote...
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(A1:P1,{"a";"g";"m"},"")=A1:P1))
<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(A1:P1,X,"")=A1:P1))
<COUNTA(X)))

for a more general approach.

Excel Count Functions
 

"Rick Rothstein" wrote...
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(A1:P1,"*[agm]*")

This would allow counting any valid LIKE pattern.

Excel Count Functions
 

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(A1:P1),{"a";"g";"m"},"")=A1:P1)) <3))

--
Rick (MVP - Excel)


"Harlan Grove" wrote in message
...
Gasbag wrote...
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(A1:P1,{"a";"g";"m"},"")=A1:P1))
<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(A1:P1,X,"")=A1:P1))
<COUNTA(X)))

for a more general approach.

Excel Count Functions
 

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(A1:P1,"agm")

--
Rick (MVP - Excel)


"Harlan Grove" wrote in message
...
"Rick Rothstein" wrote...
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(A1:P1,"*[agm]*")

This would allow counting any valid LIKE pattern.

Excel Count Functions
 

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(A1:P1,"agm",TRUE)

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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(A1:P1,"agm")

--
Rick (MVP - Excel)


"Harlan Grove" wrote in message
...
"Rick Rothstein" wrote...
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(A1:P1,"*[agm]*")

This would allow counting any valid LIKE pattern.

Excel Count Functions
 

Hi,

You can also try something like this. In a separate range , day D10:D12,
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

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

Excel Count Functions
 

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(A1:P1),{"a";"g";"m"},"")=A1:P1)) <3),--(ISTEXT(A1:P1)))

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

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
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(A1:P1),{"a";"g";"m"},"")=A1:P1)) <3))

--
Rick (MVP - Excel)


"Harlan Grove" wrote in message
...
Gasbag wrote...
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(A1:P1,{"a";"g";"m"},"")=A1:P1))
<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(A1:P1,X,"")=A1:P1))
<COUNTA(X)))

for a more general approach.

Excel Count Functions
 

"Rick Rothstein" wrote...
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.

Excel Count Functions
 

"T. Valko" wrote...
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(A1:P1),
{"a";"g";"m"},"")=A1:P1))<3),--(ISTEXT(A1:P1)))
....

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

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

Excel Count Functions
 

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.

--
Rick (MVP - Excel)


"Harlan Grove" wrote in message
...
"Rick Rothstein" wrote...
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.