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

D

donners6

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.
 
P

Paul Black

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
 
D

Dodo

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.
Click to expand...

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

Bob Phillips

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

donners6

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
 
D

donners6

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
 
D

donners6

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 said:
Hi!
=COUNTIF(A2:Z35000;"*"&"text"&"*")
Click to expand...

Less "cryptic":

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

Biff
Click to expand...
 
D

donners6

Thanks Bob,

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

Bob Phillips said:
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)
Click to expand...
 
D

Dodo

Hi Dodo (fantastic name),
Click to expand...

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...
Click to expand...

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!
 
D

donners6

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
 
D

Dodo

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'.
Click to expand...

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).
 
Top