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

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?

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?

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?

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?

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?

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?

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?

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

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.

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.

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.

"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))))

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?