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]

David,

Follows one formula in Spanish

=SUMA(SI(A2:A1000<>"",1+(LARGO(A2:A1000)-SI(ESNUMERO(HALLAR(" y ",A2:A1000)),LARGO(SUSTITUIR(A2:A1000," y ","||")),LARGO(A2:A1000)))+LARGO(A2:A1000)-LARGO(SUSTITUIR(A2:A1000,",",""))))

With this formula, try exactly the steps from my last e-mail

By the way, are you using PC/Windows or Mac?

M.

 

[davidazevedo]

Thanks once more.

No. I'm using a portuguese version of Microsoft Excel 2003 SP3. I live in Spain and at work I use a spanish version.

1. Select the translated formula and copy it (Ctrl+C)
2. Choose a cell in your worksheet to house the formula and click inside this cell
3. Put the cursor inside the formula-bar and paste the formula (Ctrl+V)
4. With the cursor still inside the formula-bar press and keep pressed the keys Ctrl and Shift
5. With Ctrl and Shift still pressed, hit Enter

It's exactly what I've been doing but it still doesn't work.

 

[Marcelo Branco]

What are you getting? An error message? PC/Windows or Mac?

M.

 

[davidazevedo]

I'm using a PC, windows 7.

This is the error message I get:

 

[Marcelo Branco]

David,

Oh, now i know what is going wrong

In Excel 2007 or 2003 pt-version the function PESQUISAR (Excel 2010 pt-version) is called LOCALIZAR.

Exactly the same function, but with a different name (please, dont ask me why )

So, try these
in B2 (attention: this is an array-formula)
=SOMA(SE(A2:A1000<>"";1+(NÚM.CARACT(A2:A1000)-SE(ÉNÚM(LOCALIZAR(" y ";A2:A1000));NÚM.CARACT(SUBSTITUIR(A2:A1000;" y ";"@@"));NÚM.CARACT(A2:A1000)))+NÚM.CARACT(A2:A1000)-NÚM.CARACT(SUBSTITUIR(A2:A1000;",";""))))
Ctrl+Shift+Enter

in C2 (just for checking-purposes)
=1+(NÚM.CARACT(A2)-SE(ÉNÚM(LOCALIZAR(" y ";A2));NÚM.CARACT(SUBSTITUIR(A2;" y ";"@@"));NÚM.CARACT(A2)))+NÚM.CARACT(A2)-NÚM.CARACT(SUBSTITUIR(A2;",";""))
just Enter and copy down

HTH

M.

 

[jasonb75]

Jason's formulas in Portuguese:

=SOMA(CONT.SE(A2:A1000;REPT({"*,*"\"* y *"};LIN(1:10)));--(NÚM.CARACT(A2:A1000)>0))
Ctrl+Shift+Enter

Depending on which excel-version you are using maybe you have to change in the second formula "\" by "."

M.

Marcelo, I just had alook and get a different version to yours when translated.

Portuguese

=SOMA(CONTAR.SE(A2:A1000;REPETIR({"*,*"\"* y *"};LIN(1:10)));--(NÚM.CARACT(A2:A1000)>0))

Spanish

=SUMA(CONTAR.SI(A2:A1000;REPETIR({"*,*"\"* y *"};FILA(1:10)));--(LARGO(A2:A1000)>0))

Both array entered.

 

[Marcelo Branco]

Jason,

To complicate the things a little bit more, there are two different Excel versions in Portuguese: brazilian and portuguese versions.

The functions have different names in each one.

Yesterday, i sent my file (saved as Excel 2003) to David. Hope that solves the translation problems.

M.

 

[jasonb75]

Jason,

To complicate the things a little bit more, there are two different Excel versions in Portuguese: brazilian and portuguese versions.

The functions have different names in each one.

Yesterday, i sent my file (saved as Excel 2003) to David. Hope that solves the translation problems.

M.

I'm sure microsoft do this purely to confuse us I tried changing my language settings to see if it would auto-translate, the delimiters changed, but the functions remained in english.

 

[Marcelo Branco]

I'm sure microsoft do this purely to confuse us I tried changing my language settings to see if it would auto-translate, the delimiters changed, but the functions remained in english.

Jason,

As i have, on a dayly base, to translate my formulas to english and vice-versa i have these two macros in my Personal Workbook

Sub YOURLANGUAGEtoENG()
    ActiveCell.Offset(1, 0) = "'" & ActiveCell.Formula
End Sub
 
Sub ENGtoYOURLANGUAGE()
    ActiveCell.Offset(1, 0) = ActiveCell.Formula
End Sub

I put the formula in an empty cell. run the macro, and got what i need in the cell immediately below.

M.

 

[davidazevedo]

You guys are great! Thank you so much for your help.

I have the following formula working properly:

=SOMA(SE(A1:A7<>"";1+(NÚM.CARACT(A1:A7)-SE(É.NÚM(PROCURAR(" y ";A1:A7));NÚM.CARACT(SUBST(A1:A7;" y ";"@@"));NÚM.CARACT(A1:A7)))+NÚM.CARACT(A1:A7)-NÚM.CARACT(SUBST(A1:A7;",";""))))

It's portuguese. The thing is that in spanish the word "y" (meaning "and" in english) is changed to "e" if the next word starts with an i sound. If I write "Tom e Isabel" it will count as one person.
Could anyone please complete the formula above so that it doesn't count "e", the same way it does to "y"?