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

 

[davidazevedo]

Thank you all for you help.

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

I am currently living in Spain and the >3 formula will actually be a >1 since "and" is translated to "y". No problem there.

The issue is with names such as Peggy Sue, which will count as 2. No way to solve that!

I tried to install the HTML Maker but I can't find the menu in excel, so here's a public dropbox link to the excel file: http://dl.dropbox.com/u/13441108/Tables.xls

 

[jasonb75]

Jim, does this make it easier to understand?

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 183px"><COL style="WIDTH: 21px"><COL style="WIDTH: 21px"><COL style="WIDTH: 14px"><COL style="WIDTH: 21px"><COL style="WIDTH: 14px"><COL style="WIDTH: 21px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">romeo and juliet</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">brad and angelina</TD><TD style="TEXT-ALIGN: right">17</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">ashton and demi</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">bruce</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Chico, Harpo and Groucho</TD><TD style="TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">10</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=LEN(A1)</TD></TR><TR><TD>C1</TD><TD>=LEN(SUBSTITUTE(A1," ",""))</TD></TR><TR><TD>D1</TD><TD>=B1+1-C1</TD></TR><TR><TD>E1</TD><TD>=LEN(SUBSTITUTE(A1,"and",""))</TD></TR><TR><TD>F1</TD><TD>=(B1-E1)/3</TD></TR><TR><TD>G1</TD><TD>=D1-F1</TD></TR><TR><TD>G6</TD><TD>=SUM(G1:G5)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

All formula in B1:G1 filled down to row 5.

 

[jeffreybrown]

I'm sorry but the machine I am on right now will not let me get to your link. It's blocked.

It's seems to me a formula that will give you one answer in one quick go is going to be hard, at least for me, to achieve. As stated before, if the customers put names in seperate cells then you could easily make some good counts.

Did you look at the link I provided? Will that help any? At

I think I am at a loss with how to help here.

Since this thread count is rather high, a bump may not get much attention so you could possible repost with a new thread and a link to this post.

Sorry

 

[jasonb75]

I'm going to throw in a new (possible) solution, been playing around with jeff's formula and managed to make it trip up a few times, could be easily fixed, but this is probably more managable.

=SUMPRODUCT(--ISNUMBER(SEARCH({"* and *",","},A1:A5)))+COUNTA(A1:A5)

When you translate this note that the word and has a single space before, and after, without this you will 'drop' guests from the total. (see example below using jeff's formula without the spaces).

Excel Workbook
	A	B
1	andrew and andrea	5
2	brad and angelina	10
3	ashton and demi
4	bruce
5	andrew, andre and andrea
Sheet

 

[Marcelo Branco]

The issue is with names such as Peggy Sue, which will count as 2. No way to solve that!

Maybe this

<TABLE style="WIDTH: 319pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=425><COLGROUP><COL style="WIDTH: 271pt; mso-width-source: userset; mso-width-alt: 13202" width=361><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #8db4e3; WIDTH: 271pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=361>Guests</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>Count</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>romeo y juliet


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>brad y angelina</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>ashton y demi</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>bruce, pamela y Peggy Sue</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Chico, Harpo y Groucho Marx</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Mike</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Michael Cayne, Richard Gere, Liz Taylor y Richard Burton</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>4</TD></TR></TBODY></TABLE>

Formula in B2
=1+(LEN(A2)-IF(ISNUMBER(SEARCH(" y ",A2)),LEN(SUBSTITUTE(A2," y ",""))+2,LEN(A2)))+LEN(A2)-LEN(SUBSTITUTE(A2,",",""))

HTH

M.

 

[jasonb75]

=SUM(COUNTIF(A:A,{"*","* y *","*,*"}))

edit: Marcelo, can you break it?

 

[Marcelo Branco]

Hi Jason,

Your formula (beautiful!) results in 20 with my example above.

My counting is 21...

I have to say that i still have a problem with entries like
Barack y Michelle Obama, Bill y Hilary Clinton

Still trying to solve...



M.

 

[Marcelo Branco]

-EDIT-

Jason,

Your counting is 19 (i had forgotten to erase the header "Guests")

M.

 

[jasonb75]

Hi Jason,

Your formula (beautiful!) results in 20 with my example above.

My counting is 21...

I have to say that i still have a problem with entries like
Barack y Michelle Obama, Bill y Hilary Clinton

Still trying to solve...



M.

Same problem here, just re-checked, the countif formula only counts each criteria once per cell, wonder if there is a way around it

 

[jim may]

Jasonb75,

Most COOL!!,

Jim