Counting the number of times a word appears with a twist

Thanks very much for the response.

1. Workbook attached to the original post now.

2. I wish to count how often a word appears through many comments. A single comment could have the word several times. Each time the word appears, no matter where it appears in the comment, I want to count it.

Why is this important? For example when someone is irritated they use polite terms more often than someone who is not.

Breakdown of the example:
Yes - Fox - Fox: Fence: 2
No - Bear; Rabbit - Bear: Fence 2, Rabbit: Fence 1 = Fence:3
Partial - Rabbit - Rabbit: Fence: 4

3. In my real life case I have to work with comments taken from a digital corpus.

I have assigned them one of three values, being either valid (yes), invalid (no) or partially valid (partial).

When I am having to deal with comments, certain words are important for me to know as they could indicate validity.

Okay, let me start over:

We are a company that make 3D models of furniture. When we submit a 3D model to the customer, they Quality Assure the model and either approve the model or place in a rework comment.

I work with transferring information between a customer’s extranet and our internal system, specifically with the rework comments. This is lots of copy and pasting.

However, the rework comments that they submit are influenced by the settings in their render engine.
The render engine handles, reflectivity, lighting, colour saturation and numerous other functions. It’s ultimately a very customisable light box (It’s similar in function to what professional photographers use for taking pictures of furniture on a white background).

However, their quality assurance people assume that what they see is what we have offered. So they may ask to make the model lighter, when in reality it’s a render engine issue (they just need to adjust the lighting).

This causes there to be degrees of ambiguity in their comments.

Ambiguity when transferring the rework comment to our very rigid minded non-English speaking 3D modellers leads to confusion and loss of time.

For me to clear up the ambiguity, I read the review comment and set the rework comment in one of three states. A rework comment cannot have more than one state. The three states are: Valid, Invalid, or Partially Valid.

What I have noticed is that there are certain words that show up more often in invalid comments. However, this is perception and I don’t have any hard data to back this up. Hence, I need to measure it.

After reviewing 950 model rework comments yesterday, I now have a list of common words found in those rework comments.

What I want to do is find out how commonly a word from that list will show up in one of the three categories: Valid, Invalid, Partially Valid.

Those words sometimes repeat themselves. For example in an invalid comment:
“Make the wood top lighter. Make the marble sides lighter.”

In this case I want to count the invalid comment with the word lighter, twice.

This then means that I have data to infer how often invalid comments relate to say lighting issues.

Just for the sake of making things easier I have created a new excel sheet which deals with 3D Models and have attached it here.

Thank you!!

It works perfectly.

I have now learned something new.

Thanks again!