hello,
I'm trying to count the occurrence of the the word se in a range of a column. preferably a function
for example the column would have the words.
self
lesser
presses
se
second
super se
set
if you use a function, it should only return 2 count result and not 7, even if all the words/sentences i listed have the characters/word se in them.
thanks
Last edited by kishin; 07-11-2012 at 01:22 PM.
=countif(A1:A7,"*se")
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
oeldere thanks for your help. The only problem I have with your solution is what if Se is the first word in the sentence, the star wouldnt work.Originally Posted by oeldere
=countif(A1:A7,"*se")
I don't get it, see my example.
Otherwise post your example to show me.
Oeldere, I saw your spreadsheet and your function works fine but, I forgot to add some requirments.
What if se is at the beginning of a sentence with multiple words following it. Then your function wouldn't count the sentence string with se being the first word of the sentence.
e.g.
se
that is se
se says this
i think your function would return 2 count, when i would expect a 3 count. sorry for my ambiguity.
Try:
=SUMPRODUCT(--ISNUMBER(SEARCH("* se *"," "&A1:A7&" ")))
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
NBVC thanks for your help, this function seems to work fine, but only 1 minor problem. what if the word se had a comma or period, punctuation after it. Is there a way to modify the function to allow for that without making it count words with letters after the word se?
like this perhaps
"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
ah ah should have added the blank to my sumproduct attempt,then lost the plot
Just to wind you up a bit Martin ...
Formula:
=SUM(--(ISNUMBER(SEARCH(" se "," "&A2:A7&" ",1))))
Enter with Ctrl+Shift+Enter not just Enter. ...
Something like:
=SUMPRODUCT(--ISNUMBER(SEARCH({"* se *","* se. *","* se, *"}," "&A1:A7&" ")))
or
=SUMPRODUCT(--ISNUMBER(SEARCH("* se"&{" *",". *","*, *"}," "&A1:A7&" ")))
NBVC thanks, I think that worked.
I would like to thank everyone for taking the time to help.
I'm sorry if i was rude or unclear.
I did do many google searches on this and forums searches, and people would solve it by countif(range,"*word*"), but that didnt solve my problem of a word/character that could be within another existing word in the column.
thx again folks! cheers!
edit: if i find more problems regarding this I will post here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks