+ Reply to Thread
Results 1 to 15 of 15

How can you count if the same word has been used in a Spreadsheet?

  1. #1
    donners6
    Guest

    How can you count if the same word has been used in a Spreadsheet?

    i.e. a standard spreadsheet has a list of different names (random) and I want
    to do a 'word count' as such e.g. how many times 'Donnelly' appears in the
    spreadsheet.

  2. #2
    Paul Black
    Guest

    Re: How can you count if the same word has been used in a Spreadsheet?

    Hi donners6,

    Assuming there are NO Names that you want to Find in Column "A", Insert
    this Formula in Cell "A1" :-
    =COUNTIF(B:IV,"=Donnelly")

    Hope this Helps.
    All the Best.
    Paul


  3. #3
    Dodo
    Guest

    Re: How can you count if the same word has been used in a Spreadsheet?

    "=?Utf-8?B?ZG9ubmVyczY=?=" <[email protected]> wrote in
    news:[email protected]:

    > i.e. a standard spreadsheet has a list of different names (random) and
    > I want to do a 'word count' as such e.g. how many times 'Donnelly'
    > appears in the spreadsheet.
    >


    If you want to find the search text also as part of text in a cell, put
    this in a cell outside the range:

    =COUNTIF(A2:Z35000;"*"&"text"&"*")

    Otherwise leave the widcards out:

    =COUNTIF(A2:Z35000;"text")


    --

    It is I, DeauDeau
    (Free after monsieur Leclerc in 'Allo, 'allo)

  4. #4
    Biff
    Guest

    Re: How can you count if the same word has been used in a Spreadsheet?

    Hi!

    >=COUNTIF(A2:Z35000;"*"&"text"&"*")


    Less "cryptic":

    =COUNTIF(A2:Z35000,"*text*")

    Biff

    "Dodo" <[email protected]> wrote in message
    news:[email protected]...
    > "=?Utf-8?B?ZG9ubmVyczY=?=" <[email protected]> wrote in
    > news:[email protected]:
    >
    >> i.e. a standard spreadsheet has a list of different names (random) and
    >> I want to do a 'word count' as such e.g. how many times 'Donnelly'
    >> appears in the spreadsheet.
    >>

    >
    > If you want to find the search text also as part of text in a cell, put
    > this in a cell outside the range:
    >
    > =COUNTIF(A2:Z35000;"*"&"text"&"*")
    >
    > Otherwise leave the widcards out:
    >
    > =COUNTIF(A2:Z35000;"text")
    >
    >
    > --
    >
    > It is I, DeauDeau
    > (Free after monsieur Leclerc in 'Allo, 'allo)




  5. #5
    Dodo
    Guest

    Re: How can you count if the same word has been used in a Spreadsheet?

    "Biff" <[email protected]> wrote in news:OX$DXA5oFHA.2472
    @tk2msftngp13.phx.gbl:

    > Hi!
    >
    >>=COUNTIF(A2:Z35000;"*"&"text"&"*")

    >
    > Less "cryptic":
    >
    > =COUNTIF(A2:Z35000,"*text*")
    >


    Right!

    But with my example you can replace "text" with e.g B1 to search for the
    text entered in B1. ;-)))


    --

    It is I, DeauDeau
    (Free after monsieur Leclerc in 'Allo, 'allo)

  6. #6
    Biff
    Guest

    Re: How can you count if the same word has been used in a Spreadsheet?

    >But with my example you can replace "text" with e.g B1 to search for the
    >text entered in B1. ;-)))


    True!

    Biff

    "Dodo" <[email protected]> wrote in message
    news:[email protected]...
    > "Biff" <[email protected]> wrote in news:OX$DXA5oFHA.2472
    > @tk2msftngp13.phx.gbl:
    >
    >> Hi!
    >>
    >>>=COUNTIF(A2:Z35000;"*"&"text"&"*")

    >>
    >> Less "cryptic":
    >>
    >> =COUNTIF(A2:Z35000,"*text*")
    >>

    >
    > Right!
    >
    > But with my example you can replace "text" with e.g B1 to search for the
    > text entered in B1. ;-)))
    >
    >
    > --
    >
    > It is I, DeauDeau
    > (Free after monsieur Leclerc in 'Allo, 'allo)




  7. #7
    Bob Phillips
    Guest

    Re: How can you count if the same word has been used in a Spreadsheet?

    In that instance it would be more beneficial to the OP to give specific
    examples, such as

    =COUNTIF(A2:Z35000,"*text*")

    or if the texct is in a cell, then use

    =COUNTIF(A2:Z35000;"*"&B1&"*")

    otherwise the OP might be confused.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Dodo" <[email protected]> wrote in message
    news:[email protected]...
    > "Biff" <[email protected]> wrote in news:OX$DXA5oFHA.2472
    > @tk2msftngp13.phx.gbl:
    >
    > > Hi!
    > >
    > >>=COUNTIF(A2:Z35000;"*"&"text"&"*")

    > >
    > > Less "cryptic":
    > >
    > > =COUNTIF(A2:Z35000,"*text*")
    > >

    >
    > Right!
    >
    > But with my example you can replace "text" with e.g B1 to search for the
    > text entered in B1. ;-)))
    >
    >
    > --
    >
    > It is I, DeauDeau
    > (Free after monsieur Leclerc in 'Allo, 'allo)




  8. #8
    donners6
    Guest

    Re: How can you count if the same word has been used in a Spreadsh

    Hi Paul,

    Thanks for reply. I am not sure if I've been specific enough (excuse my
    Excel ignorance). The column 'E' has people’s names from cell '6' onwards
    (some cells can have several names). Basically I want to 'find' how many
    times e.g. 'Donnelly' has been entered in that column (or whole spreadsheet).
    I put your formula into 'E1' with no success...

    To me it is more of a specific 'word' count that I need to do...I am not
    even sure if this is available in 'Word' (another question!)...

    Thanks for your help,
    Paul


    "Paul Black" wrote:

    > Hi donners6,
    >
    > Assuming there are NO Names that you want to Find in Column "A", Insert
    > this Formula in Cell "A1" :-
    > =COUNTIF(B:IV,"=Donnelly")
    >
    > Hope this Helps.
    > All the Best.
    > Paul
    >
    >


  9. #9
    donners6
    Guest

    Re: How can you count if the same word has been used in a Spreadsh

    Hi Dodo (fantastic name),

    I entered your formula into cell 'E2' (changed the 'A2' to 'E2' and entered
    "Donnelly"...no joy...see my reply to the first response by Paul Black for
    some clarity...

    Thanks for the response,
    Paul

    "Dodo" wrote:

    > "=?Utf-8?B?ZG9ubmVyczY=?=" <[email protected]> wrote in
    > news:[email protected]:
    >
    > > i.e. a standard spreadsheet has a list of different names (random) and
    > > I want to do a 'word count' as such e.g. how many times 'Donnelly'
    > > appears in the spreadsheet.
    > >

    >
    > If you want to find the search text also as part of text in a cell, put
    > this in a cell outside the range:
    >
    > =COUNTIF(A2:Z35000;"*"&"text"&"*")
    >
    > Otherwise leave the widcards out:
    >
    > =COUNTIF(A2:Z35000;"text")
    >
    >
    > --
    >
    > It is I, DeauDeau
    > (Free after monsieur Leclerc in 'Allo, 'allo)
    >


  10. #10
    donners6
    Guest

    Re: How can you count if the same word has been used in a Spreadsh

    Thanks Biff,

    Yep, gave these a go...no joy...

    See, my reply to the first reply from Paul Black for more clarity...

    Much appreciated,
    Paul

    "Biff" wrote:

    > Hi!
    >
    > >=COUNTIF(A2:Z35000;"*"&"text"&"*")

    >
    > Less "cryptic":
    >
    > =COUNTIF(A2:Z35000,"*text*")
    >
    > Biff
    >
    > "Dodo" <[email protected]> wrote in message
    > news:[email protected]...
    > > "=?Utf-8?B?ZG9ubmVyczY=?=" <[email protected]> wrote in
    > > news:[email protected]:
    > >
    > >> i.e. a standard spreadsheet has a list of different names (random) and
    > >> I want to do a 'word count' as such e.g. how many times 'Donnelly'
    > >> appears in the spreadsheet.
    > >>

    > >
    > > If you want to find the search text also as part of text in a cell, put
    > > this in a cell outside the range:
    > >
    > > =COUNTIF(A2:Z35000;"*"&"text"&"*")
    > >
    > > Otherwise leave the widcards out:
    > >
    > > =COUNTIF(A2:Z35000;"text")
    > >
    > >
    > > --
    > >
    > > It is I, DeauDeau
    > > (Free after monsieur Leclerc in 'Allo, 'allo)

    >
    >
    >


  11. #11
    donners6
    Guest

    Re: How can you count if the same word has been used in a Spreadsh

    Thanks Bob,

    Mmm, no luck though, this has gone a bit over my head...

    "Bob Phillips" wrote:

    > In that instance it would be more beneficial to the OP to give specific
    > examples, such as
    >
    > =COUNTIF(A2:Z35000,"*text*")
    >
    > or if the texct is in a cell, then use
    >
    > =COUNTIF(A2:Z35000;"*"&B1&"*")
    >
    > otherwise the OP might be confused.
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Dodo" <[email protected]> wrote in message
    > news:[email protected]...
    > > "Biff" <[email protected]> wrote in news:OX$DXA5oFHA.2472
    > > @tk2msftngp13.phx.gbl:
    > >
    > > > Hi!
    > > >
    > > >>=COUNTIF(A2:Z35000;"*"&"text"&"*")
    > > >
    > > > Less "cryptic":
    > > >
    > > > =COUNTIF(A2:Z35000,"*text*")
    > > >

    > >
    > > Right!
    > >
    > > But with my example you can replace "text" with e.g B1 to search for the
    > > text entered in B1. ;-)))
    > >
    > >
    > > --
    > >
    > > It is I, DeauDeau
    > > (Free after monsieur Leclerc in 'Allo, 'allo)

    >
    >
    >


  12. #12
    donners6
    Guest

    Re: How can you count if the same word has been used in a Spreadsh

    Biff,

    Yep, it has gone a bit over my head now,

    Thanks anyway,
    Paul

    "Biff" wrote:

    > >But with my example you can replace "text" with e.g B1 to search for the
    > >text entered in B1. ;-)))

    >
    > True!
    >
    > Biff
    >
    > "Dodo" <[email protected]> wrote in message
    > news:[email protected]...
    > > "Biff" <[email protected]> wrote in news:OX$DXA5oFHA.2472
    > > @tk2msftngp13.phx.gbl:
    > >
    > >> Hi!
    > >>
    > >>>=COUNTIF(A2:Z35000;"*"&"text"&"*")
    > >>
    > >> Less "cryptic":
    > >>
    > >> =COUNTIF(A2:Z35000,"*text*")
    > >>

    > >
    > > Right!
    > >
    > > But with my example you can replace "text" with e.g B1 to search for the
    > > text entered in B1. ;-)))
    > >
    > >
    > > --
    > >
    > > It is I, DeauDeau
    > > (Free after monsieur Leclerc in 'Allo, 'allo)

    >
    >
    >


  13. #13
    Dodo
    Guest

    Re: How can you count if the same word has been used in a Spreadsh

    "=?Utf-8?B?ZG9ubmVyczY=?=" <[email protected]> wrote in
    news:[email protected]:

    > Hi Dodo (fantastic name),


    Yeh, I'm extinct! ;-)))

    > I entered your formula into cell 'E2' (changed the 'A2' to 'E2' and
    > entered "Donnelly"...no joy...see my reply to the first response by
    > Paul Black for some clarity...
    >


    I suppose your separator is , (if not replace the , in the following
    example with your local separator which here in NL is ; which I forgot to
    replace in my earlier examples, sorry).

    You have to enter the formula in a cell outside the area you are searching!

    So, suppose the search range is columns A:D (this will search the columns
    top to bottom; if not desired you have to make it a range like e.g.
    A2:D34).

    Then you can enter in cell E2:

    =COUNTIF(A:D,"*"&E3&"*")

    Then in cell E3 you can enter the search term: Donnelly (no quotes!) and
    you will see the result in cell E2.
    So, do not enter the search term in the same cell as where you entered the
    formula!
    (If that is what happened.)

    I do not know your level of experience with Excel so in case I made some
    assumptions about your Excel skills that irritate you, I apologize in
    advance!


    --

    It is I, DeauDeau
    (Free after monsieur Leclerc in 'Allo, 'allo)

  14. #14
    donners6
    Guest

    Re: How can you count if the same word has been used in a Spreadsh

    Hi Dodo,

    OK, let's see...the name/word 'Donnelly' is in numerous cells in column 'E'
    i.e. 'E6' onwards...I've entered your formula '=COUNTIF(A:D,"*"&E3&"*")' into
    cell 'E2' and 'Donnelly' into cell 'E3'...but I am still getting '0' in cell
    'E2'.

    Close but no cigar!

    Thanks for the help,
    Paul


    "Dodo" wrote:

    > "=?Utf-8?B?ZG9ubmVyczY=?=" <[email protected]> wrote in
    > news:[email protected]:
    >
    > > Hi Dodo (fantastic name),

    >
    > Yeh, I'm extinct! ;-)))
    >
    > > I entered your formula into cell 'E2' (changed the 'A2' to 'E2' and
    > > entered "Donnelly"...no joy...see my reply to the first response by
    > > Paul Black for some clarity...
    > >

    >
    > I suppose your separator is , (if not replace the , in the following
    > example with your local separator which here in NL is ; which I forgot to
    > replace in my earlier examples, sorry).
    >
    > You have to enter the formula in a cell outside the area you are searching!
    >
    > So, suppose the search range is columns A:D (this will search the columns
    > top to bottom; if not desired you have to make it a range like e.g.
    > A2:D34).
    >
    > Then you can enter in cell E2:
    >
    > =COUNTIF(A:D,"*"&E3&"*")
    >
    > Then in cell E3 you can enter the search term: Donnelly (no quotes!) and
    > you will see the result in cell E2.
    > So, do not enter the search term in the same cell as where you entered the
    > formula!
    > (If that is what happened.)
    >
    > I do not know your level of experience with Excel so in case I made some
    > assumptions about your Excel skills that irritate you, I apologize in
    > advance!
    >
    >
    > --
    >
    > It is I, DeauDeau
    > (Free after monsieur Leclerc in 'Allo, 'allo)
    >


  15. #15
    Dodo
    Guest

    Re: How can you count if the same word has been used in a Spreadsh

    "=?Utf-8?B?ZG9ubmVyczY=?=" <[email protected]> wrote in
    news:[email protected]:

    > Hi Dodo,
    >
    > OK, let's see...the name/word 'Donnelly' is in numerous cells in
    > column 'E' i.e. 'E6' onwards...I've entered your formula
    > '=COUNTIF(A:D,"*"&E3&"*")' into cell 'E2' and 'Donnelly' into cell
    > 'E3'...but I am still getting '0' in cell 'E2'.


    The formula in text:

    =COUNTIF(Range_to_be_searched,Text_to_be_searched_for)

    In E6 and onwards? Down from E6? Yes?

    In your case the search range now should be: E6:E1000 (or whatever the last
    cell down is).

    So:

    In E2:

    =COUNTIF(E6:E1000,"*"&E3&"*")

    Does it work now?

    If searching has to be over more columns, the search range could e.g. be:
    E6:K1543 (or wherever you have put the text away).


    --

    It is I, DeauDeau
    (Free after monsieur Leclerc in 'Allo, 'allo)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1