Re: 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" <GaryMc@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:31731C01-C116-45B2-9976-98CD8C091542@xxxxxxxxxxxxxxxx
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?



.



Relevant Pages

  • Re: number formatting help
    ... What I didn't know was that the double unary minus would covert the text string to a number and thereby lose the leading zero's, I've only ever used '--' in SUMPRODUCT formulas. ... "David Biddulph" wrote in message ... "Dave Peterson" wrote in message ...
    (microsoft.public.excel.misc)
  • Re: editing numbers
    ... Function trimchar(MyString As String, ... "David Biddulph" wrote: ... "stremetzky" wrote: ... How can I erase the first one or two zeros?? ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Can I Use a Count Function for Text?
    ... letter occurs in that cell and eventually count all occurances of all letters ... and t occurs in that string. ... "David Biddulph" wrote: ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Indirect function across sheets
    ... It was the placement of the quotes that was the ... "David Biddulph" wrote: ... CONCATENATE to glue the final string together. ... but then want to use the indirect function to ...
    (microsoft.public.excel.misc)
  • Re: CF for specific ranges of time for an entire column
    ... If you are seeing the quotation marks, take them out, ... Excel assumes that it is a text string and puts the quote marks around ... "David Biddulph" wrote: ... result was every cell from D3:D30 comparing to F2. ...
    (microsoft.public.excel.misc)