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.
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.
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
"=?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)
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)
"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)
>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)
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)
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
>
>
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)
>
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)
>
>
>
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)
>
>
>
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)
>
>
>
"=?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)
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)
>
"=?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)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks