Count Names from Cell

J

Johnboy28

Board Regular
Joined
Jun 22, 2013
Messages
172
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
Hi
Does anyone know if there is a formula that picks up namesfrom a Cell and count them?

Please see below example.

Thank you.

NumberNamesJohnnyMarkDaveJasonJohnTinaBelindaChrisSteven
3Johnny, Mark, Dave, Jason, Dave112??
5Gavin, Mark, Peter, John
6Tina, Belinda, Johnny, Chris, Steven
7Tina, Johnny

<colgroup><col style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;" width="74"> <col style="width: 186pt; mso-width-source: userset; mso-width-alt: 9069;" width="248"> <col style="width: 16pt; mso-width-source: userset; mso-width-alt: 768;" width="21"> <col style="width: 48pt;" width="64"> <col style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;" width="53"> <col style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;" width="55"> <col style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;" width="54"> <col style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;" width="55"> <col style="width: 38pt; mso-width-source: userset; mso-width-alt: 1865;" width="51"> <col style="width: 48pt;" width="64"> <col style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;" width="53"> <col style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;" width="58"> <tbody>
</tbody>
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Hi, given in A1:

Excel 2010
ABCDEFGHIJKL
1NumberNamesJohnnyMarkDaveJasonJohnTinaBelindaChrisSteven
23Johnny, Mark, Dave, Jason, Dave112110000
35Gavin, Mark, Peter, John010010000
46Tina, Belinda, Johnny, Chris, Steven100011111
57Tina, Johnny100011000
Johnboy28



Formula in D2 is =SUMPRODUCT((LEN($B2)-LEN(SUBSTITUTE($B2,D$1,"")))/LEN(D$1))
 
Last edited:
Upvote 0
mmmh my solution is wrong as it would consider johnny, johnn, john as the same person.
While waiting for a better answer consider this:

Excel 2010
ABCDEFGHIJKL
1NumberNamesJohnnyMarkDaveJasonJohnTinaBelindaChrisSteven
23Johnny, Mark, Dave, Jason, Dave,1121
35Gavin, Mark, Peter, John,11
46Tina, Belinda, Johnny, Chris, Steven,11111
57Tina, Johnny,11
Johnboy28


Modify data in range 'Names' and include a , after last name.
Formula in D2 is =IF(ISNUMBER(SEARCH(D$1&",",$B2)),SUMPRODUCT((LEN($B2)-LEN(SUBSTITUTE($B2,D$1,"")))/LEN(D$1)),"")
Would that be better?
 
Upvote 0
@cyrilbrd

You'll need UPPER to operate in a case-insensitive manner and create an ending comma in the formula itself:

D2, copied across and down:

=SUMPRODUCT(LEN($B2&",")-LEN(SUBSTITUTE(UPPER($B2&","),UPPER(D$1&","),"")))/LEN(D$1&",")
 
Upvote 0
Noted, so upper is not merely used to convert to capital but to ensure that all words within the string are ended with a comma... First time to encounter that. Thanks for your time and correction.
 
Upvote 0
cyrilbrd said:
Noted, so upper is not merely used to convert to capital but to ensure that all words within the string are ended with a comma... First time to encounter that. Thanks for your time and correction.
Click to expand...

Ending comma is done with the & operator. UPPER is used to escape SUBSTITUE case-sensitiveness, achieved by setting everything in upper case (LOWER would work equally well).
 
Upvote 0
yes my phrasing was incorrect, indeed tried with lower and it worked just the same.
 
Upvote 0
You can always create your own function.
Code: 
Function kool(str, delimiter, lookUp) As String
    'str is the cell that contains the values with the delimiter(e.g. Rob, Bob, Jon, Rob)
    'delimiter is the delimiter such as a comma(e.g. ,)
    'lookup is the value you are looking up to count(e.g. Rob will display 2 in this formula)
    c = 0
    Dim V() As String
    V = Split(str, delimiter)
    For i = LBound(V) To UBound(V)
    If lookUp = V(i) Then
    c = c + 1
    End If
    Next i
    kool = c
End Function
After you have copy pasted the function into your macro list, enter this formula into cell D2.
Code: 
=kool($B2,", ",D$1)
Drag the formula down and across as many rows as needed.
A
B
C
D
E
F
G
H
I
J
K
L
1
membersNamesJohnnyMarkDaveJasonJohnTinaBelindaChrisSteven
2
3Johnny, Mark, Dave, Jason, Dave112100000
3
5Dave, Jason, John, Tina, Mark, Rene011111000
4
6Tina, Belinda, Johnny, Chris, Steven100001111
5
7Tina, Johnny100001000

<tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Conditional formating where there are 2 values in a cell
Replies
3
Views
920
Godders199
G
A
  • Question
Add Sequence numbers Based on Dates
Replies
1
Views
2K
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Question
Multiple Condition IF/OR Statement with TEXT
Replies
1
Views
861
AlanY
A
H
  • Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
5K
Fluff
Fluff
N
  • Question
How to Create Multiple Reports from One Sheet
Replies
1
Views
2K
Logit
Logit

Forum statistics

Threads
1,214,911
Messages
6,122,194
Members
449,072
Latest member
DW Draft

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