Tweak formula to be non- case sensitive and also counts only exact words

My data set is a bunch of Tweets and I want to use a pre determined lexicon to count how many times certain words appear in the data set. My lexicon has a category, word in Tweet to search for, and weight of the word. Right now I am trying to find out how many times a certain word appears in the data set and then repeat that calculation again for each of the 30k words.

I figured out how to count the words but it is counting everything that is like the word and also is case sensitive. For example, it counts 'determined' when the word is 'determine' but doesn't count 'Determined'

This is the formula I am using where my data are on sheet1c54:c1783. The word to be found is in cell B54. This cell changes because there are thousands of words to find and I don't want to write in the actual words in the formula, just where they are located so I can drag down the column.

"SUMPRODUCT((LEN(Sheet1!C54:C1783)-LEN(SUBSTITUTE(Sheet1!C54:C1783,B54,"""")))/LEN(B54))"

Can you help me adjust this formula so that in a sentence like "I need to determine what my GPA is after this test." or "Determine what your GPA is." it counts 1 for each sentence but in a sentence like "I am determined to find my GPA" it counts 0.

Thank you in advance!

Glenn! You are a genius and a complete lifesaver!!! With a little tweak, your formula worked!!! Thank you SO VERY much! This is the final formula that I am using:

=SUMPRODUCT((LEN(" "&editeddata!$C$2:$C$1731&" ")-LEN(SUBSTITUTE(UPPER(" "&editeddata!$C$2:$C$1731&" "),UPPER(" "&B2&" "),"")))/LEN(" "&B2&" "))

Where data are in editeddata!$C$2:$C$1731 and the word to look up is in B2. I dragged this formula down and while the B2 changes to B3, B4... like I wanted, I figured out how to insert $ in the data sheet range so the data reference stays the same. Wala!!! It worked!!!!

Thank you thank you thank you again!!!

Thank you very much for your response!!! I really appreciate it and have found the answer with the previous user's suggestion. I am sure yours might work too! Thank you again Menem!