LinkBack URL
About LinkBacks
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
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
Last edited by 1.zer0; 06-17-2009 at 06:35 AM.
=COUNTIF(A4:C10,"hello")
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
apologies Donkey, i have changed the title so can you now allow the solution you blocked to be shown?Originally Posted by DonkeyOte
thanks
*beat me to it lol
Yes, already done - thanks.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
deleted prob solved
Thanks for your help martin
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
This should do it:
Please Login or Register to view this content.
Excel 365 user. To unblock a downloaded macro-enabled workbook, go to your "Downloads" folder > right click on the workbook name > click 'Properties' > check the 'Unblock' checkbox. You can now open the workbook.
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)
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:
where home 1 & home2 refer to your ranges and b62 contains the word you are counting.Please Login or Register to view this content.
As DonkeyOte pointed out, howeve, it only works if the word is 'standalone' rather than part of a sentence.
Last edited by deadlyduck; 06-17-2009 at 07:09 AM. Reason: Update formula for non-contiguous ranges
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.
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.
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!)
Last edited by DonkeyOte; 06-17-2009 at 07:17 AM.
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)
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
After that huge post i just made i think this works. thanks again martin
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks