Count words in multiple cells

Hello & Welcome to the Board,

If the word to exclude is always "and", this should work...

=SUMPRODUCT(LEN(A1:A5)+1-LEN(SUBSTITUTE(UPPER(A1:A5)," ","")))-SUMPRODUCT((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"and","")))/LEN("and"))

Thank you for your fast reply.

This formula will not work because I'm using a spanish and portuguese version of MS excel. Do you know the equivalent for these languages?

There's also a problem which I didn't think of before. If a person is called Jennifer Lopez it will count as two. Is it possible to simply count the "and" as 2, the "," (comma) as 3 and if there is text with no "and" nor "," simply count 1.

Try text to columns, with the space as the delimiter. This will at least get you names in individual cells.

You could then use countif() to count names > 3 characters, or manually edit to take out erroneous data.

Jeff, Neat..
Check out where I've layed out the components of your single formula;
How can I get from the components to the final answer of 10? Tks, JimExcel 2007Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

davidazevedo said:

Thank you for your fast reply.

This formula will not work because I'm using a spanish and portuguese version of MS excel. Do you know the equivalent for these languages?

Click to expand...

http://wwwhome.ewi.utwente.nl/~trieschn/excel/excel.html

This page has translations for all of the functions jeffreybrown used in the formula.

@Jason,
Thanks for the link to the version translations...great resource.

@davidazevedo,
If you look at my sig block you will see an HTML maker which you can use to post a screen shot of what you have and what you desire.

Please take a second look at all your requirements so we can try and get you the best solution possible. With the > 3 you stated, what if you have the names Sue and Jim?

Maybe you can send a list out to collect names asking for two columns, First name and Last name which will help keep your data organized and will eliminate any "and's" or "comma's". Example: What if you receive back Peter , Paul and Mary? See the space after Peter and before the comma. This will cause a problem.

Check out this site which might help you tremendously. Plan Your Party Seating with Excel. Something to consider to make your life easier.

@Jim,
Not exactly sure what you are asking. Why add up all the components when the formula in B1 gives the correct answer?

Jeff, you misunderstood my message;

I was trying to break-down you formula so that I could understand it;

Jim

Hi Jim,

That is what I thought, but wasn't 100% sure.

Somehow the consolidated formula takes B4, B5, B6 and B7 and produces 10. I wasn't able to piece the 4 together to get the answer of 10...