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
Marcelo / Jeff

A couple more alternatives, any thoughts? I've left Marcelo's last suggestion in B2 for a quick compare.

<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: 327px"><COL style="WIDTH: 323px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Guests</TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 10pt; FONT-WEIGHT: bold">TotalCount</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-SIZE: 10pt">romeo y juliet</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">21</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-SIZE: 10pt">brad y angelina</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">21</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-SIZE: 10pt">ashton y demi</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">21</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-SIZE: 10pt">bruce, pamela y Peggy Sue</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 10pt">Chico, Harpo y Groucho Marx</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-SIZE: 10pt">Mike Smith</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-SIZE: 10pt">Michael Cayne, Richard Gere, Liz Taylor y Richard Burton</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-SIZE: 10pt">Barack y Michelle Obama, Bill y Hillary Clinton</TD><TD> </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>B2</TD><TD>{=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,",",""))))}</TD></TR><TR><TD>B3</TD><TD>{=SUM(LEN(A2:A1000)-LEN(SUBSTITUTE(A2:A1000,",","")),LEN(A2:A1000)-LEN(SUBSTITUTE(A2:A1000," y ","||")),--(LEN(A2:A1000)>0))}</TD></TR><TR><TD>B4</TD><TD>{=SUM(COUNTIF(A2:A1000,REPT({"*,*","* y *"},ROW(1:10))),--(LEN(A2:A1000)>0))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

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

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Jason,

Beautiful, specially the short formula in B4 - i'm trying to fully grasp the logic behind (any tip?)

Congratulations!

(i hope the thread-author is following this)

M.
:beerchug:
 
Upvote 0
Marcelo,

There was more luck than logic in that last one :)

REPT({"*,*","* y *"},ROW(1:10)))

creates the array constant for the countif criteria, {"*,*","*,**,*".... up to 10 commas, each wrapped in wildcards, same for "* y *".

*,* will count cells holding 1 or more commas, *,**,* will count cells holding 2 or more, etc, same for * y *.

--(LEN(range)>0) counts all the non blank cells (without falling over "" formula blanks).

Then just sum the lot.

Hope that makes sense.
 
Upvote 0
Thanks so much for your effort, guys.

Yes, I've been following this thread but I'm kinda lost. I don't know exactly how to translate the formulas into portuguese / spanish.

Marcelo, could you please post the formula in portuguese?


The more I discover working with excel the more I want to know. Don't be surprised if you see a bunch of new threads started by me. :)
 
Upvote 0
davidazevedo said:
Marcelo, could you please post the formula in portuguese?
Click to expand...

Olá David,

<TABLE style="WIDTH: 391pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=521><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><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><TBODY><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: #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><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=83>Check By Row</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: yellow; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>21</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>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=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><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>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=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><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>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=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><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>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=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><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>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=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><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>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=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><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>4</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>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><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>4</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></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><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 62pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=83 align=right>21</TD></TR></TBODY></TABLE>

My formulas in Potuguese

in B2 (attention: this is an array-formula)
=SOMA(SE(A2:A1000<>"";1+(NÚM.CARACT(A2:A1000)-SE(ÉNÚM(PESQUISAR(" 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(PESQUISAR(" 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.
ps: if you want i can translate also Jason's formulas
 
Upvote 0
Jason's formulas in Portuguese:

=SOMA(NÚM.CARACT(A2:A1000)-NÚM.CARACT(SUBSTITUIR(A2:A1000;",";""));NÚM.CARACT(A2:A1000)-NÚM.CARACT(SUBSTITUIR(A2:A1000;" y ";"||"));--(NÚM.CARACT(A2:A1000)>0))
Ctrl+Shift+Enter

=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.
 
Last edited:
Upvote 0
Thank you so much, Marcelo.

Somehow excel does not accept the "@@" so I can not validate the formula. If I delete it it keeps telling me that either I did not enter enough data or there is something wrong in the formula.

What do you mean by an array-formula? Do I have to enter it with the ctrl + shift + enter? I do that but nothing happens.


EDIT 1: I am using Excel 2003 SP3
EDIT 2: It also doesn't accept Jason's formula. I must be doing something wrong.
 
Last edited:
Upvote 0
Change the @@ by something different as 11 for example.

To enter an array-formula:
Type the formula (or copy from the forum-page + paste in the formula-bar)
Hold down both Ctrl and Shift keys and hit Enter key

Take a look at the formula-bar: if everything works ok Excel wraps the formula with curly-braces, something like
{=...}

HTH

M.
 
Upvote 0
David,

If you are using a spanish-version you have, before anything, to translate the functions to spanish. Do it, for example, using Notepad.

After you have translated the formula, follow exactly these steps
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

Excel, for sure, wraps your formula with curly-braces.

If this doesnt work send me a Private Message and i'll give you my email-address, so you can send to me your workbook.

M.
 
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
899
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,958
Messages
6,175,627
Members
452,661
Latest member
Nonhle

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