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!