# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  how to count specific words in a cell range

## 1.zer0

I don't know if this is a 'count' formula or not, but i want to count the number of times a specific word is displayed in a cell range.

for example the cell range is A4:C10 and the word is 'hello'

cheers,
1.zer0

----------


## martindwilson

=COUNTIF(A4:C10,"hello")

----------


## 1.zer0

> _Your post does not comply with Rule 1 of our Forum_ RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title. 
> *To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.*
> 
> ie try to avoid using Help in your title - TIA



apologies Donkey, i have changed the title so can you now allow the solution you blocked to be shown?

thanks


*beat me to it lol

----------


## DonkeyOte

Yes, already done - thanks.

----------


## martindwilson

deleted prob solved

----------


## 1.zer0

Thanks for your help martin

----------


## 1.zer0

ok i have tried to alter it slightly to make less work in the long run but it doesn't work.

this is my formula - =COUNTIF(HOME,B62)

with HOME being the cell range C35:C59 and K35:K59 and cell B62 being the 'criteria' of the specific word i want to count.

any help would be great,
thanks

----------


## deadlyduck

This should do it:




```
Please Login or Register  to view this content.
```

----------


## DonkeyOte

The = will make no difference I'm afraid dd.

What is the output of your formula - an error or simply 0 ?  

If 0, is the word of interest embedded within longer strings in range HOME - ie assume word of interest remains hello, is hello listed on it's own in range HOME or is it embedded ie, "hello, what is your name?" ... if so you will need to think of using wildcards (not 100% watertight but may suffice here)

----------


## deadlyduck

Sorry- I didn't spot that it's a non-contiguous range.....

If you want to count the occurrence of a word (not a word embedded in a string as in "hello, there") contained in non-contiguous ranges, you could name each non-contiguous range separately and then use a variation of my suggested formula as follows:




```
Please Login or Register  to view this content.
```


where home 1 & home2 refer to your ranges and b62 contains the word you are counting.

As DonkeyOte pointed out, howeve, it only works if the word is 'standalone' rather than part of a sentence.

----------


## DonkeyOte

nor did I for that matter which means COUNTIF won't work anyway, my point was that the = isn't required in the COUNTIF... you only need to use the operator for <,<=,>=,> type tests.

----------


## 1.zer0

the cell i refer to B62 is only the word 'hello' and no it isnt in the range.

yes it "error's" rather than 0.

----------


## DonkeyOte

OK, we need to go back a bit given the non-contiguous range... could the criteria value appear within the range D35:J59 ?  If not just use C35:K59 as the range in the COUNTIF... if it could then you need to adopt a different approach, eg:

=SUMPRODUCT((MOD(COLUMN(C35:K59)-3,8)=0)*(C35:K59=B62))

EDIT: or as Martin points out just add 2 COUNTIFs together! 
(time to get some lunch obviously!)

----------


## martindwilson

AS dk says countif wont work with non contiguos named ranges
it tries to do =countif(C35:C59,K35:K59,c1) which is invalid
try =countif(C35:C59,c1)+countif(K35:K59,c1)

----------


## 1.zer0

Ok i will try to break this down because i dont really know how to give you the info you need:

cell B62 contains the word 'hello'

in the cell range C35:C59 AND K35:59 (which i have named range1) i want to see how many cells have the word 'hello'.

It is like a list so all cells are basically one word. for example the list is hello, goodbye, greetings, goodbye, goodbye, hello, greetings etc. in the cell range C35:C59 AND K35:K59.

so underneath this i want to know how many times the cell B62 (hello) occurs in that list.

hope this is simpler to understand?!?! im confusing myself now!! lol

----------


## 1.zer0

> AS dk says countif wont work with non contiguos named ranges
> it tries to do =countif(C35:C59,K35:K59,c1) which is invalid
> try =countif(C35:C59,c1)+countif(K35:K59,c1)



After that huge post i just made i think this works. thanks again martin

----------


## jackd777

The original formula mentioned in this thread only works if the word is the only one in the cell. For example, if you're counting the amount of times "apple" appears in your spreadsheet, with one word in each cell, it will find all appearances of "apple". However, if "apple" is not the first word in a cell, it won't count it. How do I count the word that way?

Here's what I mean:

  A     B           C
apple  apple      apple
apple  the apple  apple

In the above example, the countif(A1:C2,"apple") will result in "5". I want it to count the total of appearances ("6") regardless of where the word is in the cell.

----------


## ghstnmachi

Not sure if this is still needed, but you can try:

=Countif(A1:C2,"*Apple*")

If you want to count multiple ranges, you can try:

=Countif(A1:C2,"*Apple*")+Countif(A3:C3,"*Apple*")

Hope this is still helpful.  :Cool:

----------

