Re: Using COUNTIF for a word that is joined by another word in same ce

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

BUT, now have to add to the caveat:

It *also* counts "alfred" & "wilfred"<g>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"RagDyeR" <ragdyer@xxxxxxxxxxxxx> wrote in message
news:%23Lui7g7hFHA.1248@xxxxxxxxxxxxxxxxxxxxxxx
I like that.
Good idea!

Plus, it prevents Chuck from becoming "nuts".<g>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Dave Peterson" <petersod@xxxxxxxxxxxxxxxx> wrote in message
news:42D50D24.2A265BDD@xxxxxxxxxxxxxxxxxxx
But you could use something like:

=SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(UPPER(A2:A100),UPPER(C1),"")))/LEN(C
1)

So that case isn't a problem.



RagDyer wrote:
>
> Just responded to a similar question in the excel group where the OP had
> *multiple* names in a cell.
>
> A2 to A100 contains name data.
> Enter name to count in C1.
>
> Try this:
> =SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(A2:A100,C1,"")))/LEN(C1)
>
> NOW ... the caveat:
> IF you enter "Fred" in C1, this will *also* count:
> Freddy
> Freddie
> Frederico
> Fredric
> Frederick
> BUT, it will *NOT* count:
> Alfred
> Wilfred
>
> So watch out for the case sensitivity!
> --
> Regards,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
> "CLR" <croberts@xxxxxxxxxxxxxxx> wrote in message
> news:uAgCWQ0hFHA.3256@xxxxxxxxxxxxxxxxxxxxxxx
> > Very nice Dave.........I love that one but can never remember it
> > <g>...........by the way, did you see that it only counts bob/bob/bob as
> > just one "bob"? .......of course I doubt the OP will experience that
> > combination, but just a curiosity.....it's apparently counting cells
that
> it
> > finds a bob in rather than the bob's themselves...........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> >
> > "Dave Peterson" <petersod@xxxxxxxxxxxxxxxx> wrote in message
> > news:42D45B58.9B77FC30@xxxxxxxxxxxxxxxxxxx
> > > maybe
> > >
> > > =countif(a1:a10,"*bob*")
> > >
> > > But if Bob is embedded in another name, it'll get counted.
> > >
> > > Jim/Nabob/ralph
> > >
> > > (I couldn't think of another name!)
> > >
> > > James wrote:
> > > >
> > > > To make it easy, I am trying to count the number of times the name
> "Bob"
> > > > appears in a range of cells. Most of the time, "Bob" is the only
name
> in
> > the
> > > > cell, so COUNTIF would work. However, sometimes there will be 2
names
> in
> > the
> > > > same cell, like this: "Joe/Bob." What kind of formula can I use to
> count
> > the
> > > > number of times "Bob" appears in a range of cells, including the
times
> > there
> > > > are 2 names in the same cell? With the COUNTIF function, it is only
> > counting
> > > > the number of times "Bob" appears alone in a cell within the range
of
> > cells.
> > >
> > > --
> > >
> > > Dave Peterson
> >
> >

--

Dave Peterson



.

Relevant Pages

  • Re: creating a formul
    ... Please keep all correspondence within the Group, ... >> Regards, ... >>> George ...
    (microsoft.public.excel.misc)
  • Re: Using COUNTIF for a word that is joined by another word in same ce
    ... Regards, ... > Please keep all correspondence within the Group, ... but just a curiosity.....it's apparently counting cells ... >>> Dave Peterson ...
    (microsoft.public.excel.misc)
  • Re: Data row wise, formula column wise
    ... Regards, ... Please keep all correspondence within the Group, ... It was the formula I needed, as I want the destination ... >> I want to refer to these cells in a column from a2 down. ...
    (microsoft.public.excel.misc)
  • Re: I WANT to truncate my leading zeros
    ... depending on the desired final format, ... Regards, ... Please keep all correspondence within the Group, ... Selec those cells and Data>Text to Columns>Next>Next. ...
    (microsoft.public.excel.misc)
  • Re: Easy way to delete names in a workbook?
    ... > "Dave Peterson" wrote in message ... >> Regards, ... >> Please keep all correspondence within the Group, ... >>> Check Transition Navigation Keys ...
    (microsoft.public.excel.misc)