forumla to count a word that is not stand alone - help

[erky]

I am trying to get this forumla created and need some help. In my excel file I have a list of companies going down the rows. It starts at k6 and goes down to K10781. I would like to have it show in the cell about that how many times it sees the word ford and could it. I have other business in the different cells, plus why I am asking for when it sees the word ford is because it could be listed like Sawgrass Ford, Ford of Clermont, etc. Any help would be great and hopefully you understand my questions. Thank you.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(K6:K10781,ROW(K6:K10781)-ROW(K6),0,1)),--(K6:K10781="FORD"))

This is what I currently have but it doesn't work because it is only looking for the the exact word Ford not ford in a sentence like the examples I said above.

[Richard Buttrey]

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.

[erky]

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.

Changed title.

[Pete_UK]

Thanks for changing the title - I couldn't help you with a title like you had (it's against the rules !!)

I think you need to change the last bit to this:

,--(ISNUMBER(SEARCH("Ford",K6:K10781))))

which is looking to see if Ford is anywhere within each of those cells (not case-sensitive).

Hope this helps.

Pete

[erky]

That worked, thanks brother.

[Pete_UK]

There are many threads posted with that title, which gives no indication of what your problem is for future searching. Please change it to something more meaningful, or the moderators will force you to do so.

Pete

[Cutter]

@ erky

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[erky]

Thanks for doing that and yes I clicked the star icon.