Count words in multiple cells

[davidazevedo]

Hello,

I work as a wedding planner and sometimes people send me the names list and forget to count the people or simply miscount. Is there a way to count the names?

For example, I would have in cell a1 trought a5:

romeo and juliet
brad and angelina
ashton and demi
bruce
Chico, Harpo and Groucho

The total at this table would be 10 persons.

Is there a formula that counts words that have more that 3 letters?

Thanks in advance

 

[jeffreybrown]

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"))

 

[davidazevedo]

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.

 

[SteveO59L]

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.

 

[jim may]

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, Jim

Excel Workbook
	A	B	C
1	romeo and juliet	10
2	brad and angelina
3	ashton and demi
4	bruce	17
5	Chico, Harpo and Groucho	14	3
6		77
7		4.33333
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=SUMPRODUCT(LEN(A1:A5)+1-LEN(SUBSTITUTE(UPPER(A1:A5)," ","")))-SUMPRODUCT((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"and","")))/LEN("and"))
B6	=SUMPRODUCT(LEN(A1:A5))
C5	=B4-B5

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[jasonb75]

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?

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

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

 

[jeffreybrown]

@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?

 

[jim may]

Jeff, you misunderstood my message;

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

Jim

 

[jeffreybrown]

Hi Jim,

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

 

[jim may]

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...