Count words in multiple cells

D

davidazevedo

New Member
Joined
Apr 10, 2011
Messages
18
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
 
Sort by date Sort by votes
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.
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.



Marcelo Branco said:
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
Click to expand...

It's exactly what I've been doing but it still doesn't work.
 
Upvote 0
I'm using a PC, windows 7.

This is the error message I get:

excelerror.png
 
Upvote 0
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.
 
Upvote 0
Marcelo Branco said:
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.
Click to expand...

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.
 
Upvote 0
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.
 
Upvote 0
Marcelo Branco said:
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.
Click to expand...

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

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

Code: 
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.
 
Upvote 0
You guys are great! Thank you so much for your help.

I have the following formula working properly:

Code: 
=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"?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Using VBA to loop through multiple Word Templates and fill them in with Excel Data
2
Replies
14
Views
2K
John_w
J
C
  • Solved
Count Words in cell and ignore blanks
Replies
5
Views
867
DanteAmor
DanteAmor
K
  • Solved
formula for the average of cells across columns with multiple conditions
Replies
4
Views
663
kckliodna
K
T
  • Question Question
Counting cells in a column in an array based on criteria
Replies
0
Views
898
tjdickinson
T
Oakie_Uffda
  • Question Question
Split Cell into multiple without splitting words and print decent
Replies
1
Views
1K
Oakie_Uffda
Oakie_Uffda

Forum statistics

Threads
1,223,956
Messages
6,175,609
Members
452,660
Latest member
Zatman

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top