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

 

[Marcelo Branco]

Jason,

Sorry, i had changed my data

With the example above
Your formula = 16
My counting = 17

M.

 

[jasonb75]

Marcelo,

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

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

etc

 

[Marcelo Branco]

Jason,

Unfortunately i have to work now (a pity...)

People are waiting for a new report and i'm doing a PVT

Later, i'll see your formulas

M.

 

[jasonb75]

Jason,

Unfortunately i have to work now (a pity...)

People are waiting for a new report and i'm doing a PVT

Later, i'll see your formulas

M.

And while you work I think maybe

 

[Marcelo Branco]

Jason,

I think i got it (check)

<TABLE style="WIDTH: 329pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=438><COLGROUP><COL style="WIDTH: 271pt; mso-width-source: userset; mso-width-alt: 13202" width=361><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><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=xl63 height=20 width=361>Guests

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; WIDTH: 58pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=77>TotalCount</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" 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=xl64 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=xl64 align=right>21</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=xl64 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=xl64> </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=xl64 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=xl64> </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=xl64 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=xl64> </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=xl64 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=xl64> </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=xl64 height=20>Mike Smith</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=xl64> </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=xl64 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=xl64> </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" id=td_post_2679954 class=xl64 height=20>Barack y Michelle Obama, Bill y Hillary Clinton</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=xl64> </TD></TR></TBODY></TABLE>

Array-Formula in B2 to Total Counting
=SUM(IF(A2:A1000<>"",1+(LEN(A2:A1000)-IF(ISNUMBER(SEARCH(" y ",A2:A1000)),LEN(SUBSTITUTE(A2:A1000," y "," ")),LEN(A2:A1000)))+LEN(A2:A1000)-LEN(SUBSTITUTE(A2:A1000,",",""))))
CTRL+SHIFT+ENTER

HTH

M.

 

[jeffreybrown]

Hi Marcelo,

Just tested with your data and the CSE formula and it comes up with 29.

The last step before the formula is evaluated shows Sum({3;3;3;4;4;1;5;6})

and did confirm with control + shift + enter

 

[Marcelo Branco]

Jeffrey,

Probably i pasted the wrong formula (many tests)

This i have tested again (please check)

=SUM(IF(A2:A1000<>"",1+(LEN(A2:A1000)-IF(ISNUMBER(SEARCH(" y ",A2:A1000)),LEN(SUBSTITUTE(A2:A1000," y "," ")),LEN(A2:A1000)))+LEN(A2:A1000)-LEN(SUBSTITUTE(A2:A1000,",",""))))

Ctrl+Shift+Enter

Hope its ok

M.

 

[Marcelo Branco]

Jeffrey,

Something strange is happening when i translate the formula to english.

Portuguese-version = 21, but when i copy + paste to english some function changes, because if i revert the process copy from forum (english) and translate to portuguese it propduces 29.

I am trying to figure out what is going on

M.

 

[Marcelo Branco]

I figured out (one space is being deleted when i paste the formula)

=SUM(IF(A2:A1000<>"",1+(LEN(A2:A1000)-IF(ISNUMBER(SEARCH(" y ",A2:A1000)),LEN(SUBSTITUTE(A2:A1000," y "," ")),LEN(A2:A1000)))+LEN(A2:A1000)-LEN(SUBSTITUTE(A2:A1000,",",""))))

The correct is

=SUM(IF(A2:A1000<>"",1+(LEN(A2:A1000)-IF(ISNUMBER(SEARCH(" y ",A2:A1000)),LEN(SUBSTITUTE(A2:A1000," y "," ")),LEN(A2:A1000)))+LEN(A2:A1000)-LEN(SUBSTITUTE(A2:A1000,",",""))))


Two spaces between the quotes is the correct

M.

 

[Marcelo Branco]

To avoid space-problems i change them to @@

=SUM(IF(A2:A1000<>"",1+(LEN(A2:A1000)-IF(ISNUMBER(SEARCH(" y ",A2:A1000)),LEN(SUBSTITUTE(A2:A1000," y ","@@")),LEN(A2:A1000)))+LEN(A2:A1000)-LEN(SUBSTITUTE(A2:A1000,",",""))))
CTRL+SHIFT+ENTER

M.