Is there a formula that can tell me how many times a name appears in a column if there are multiple names in each cell? For example: If my C column is as follows: Jen, Darcy, Gary Darcy Darcy, Gavin Jen, Ryan Is there a way to see how many times "Jen" appears, or "Darcy"? And so on Thanks!

See if this thread helps: http://www.computing.net/answers/of... MIKE http://www.skeptic.com/

You could also use Data...Text to Columns...Delimited...Comma to separate the data into multiple cells and the count the values in the range.

Thanks!

Hi, Have you tried the solution that Mike kindly pointed out. It is a formula that will count the number of times a word occurs in a cell or a range of cells. Here is the output I got from your data B C D E 21 Jen Darcy Gavin Amanda 22 3 2 1 1 The first name 'Jen' was in cell B21 Cell B22 had this formula =xtndcount($A$22:$A$25,B21) Your data was in the four cells A22 to A25 (note the $ in the formula - the formula can then be dragged along under a series of names). Of course you will have to create the formula 'xtndcount' following the instructions: Enter the following in a standard VBA module: Public Function xtndCount _ (rngRange As Range, strFind As String, Optional strSep As String) As Double ' a function to count text in cells in a range including more ' than one occurrence of the text in a cell. An optional ' separator can be given so that words separated by something ' other than a space can be recognized ' Dim rngCell As Range Dim strContents As String Dim intSepLen As Integer Dim dblCount As Double Dim intArray(255) As Integer Dim m As Integer Dim n As Integer Dim o As Integer On Error GoTo ErrHand 'set the separator between words to a space unless a different 'separator was passed to the function If strSep = "" Then strSep = " " End If ' get length of separator intSepLen = Len(strSep) For Each rngCell In rngRange.Cells strContents = Trim(rngCell.Value) n = InStr(1, strContents, strSep) If n > 0 Then 'at least one separator, so more than one word 'put location of separators in an array o = 1 intArray(0) = 1 For m = 1 To Len(strContents) If Mid(strContents, m, intSepLen) = strSep Then intArray(o) = m + 1 o = o + 1 End If Next m intArray(o) = Len(strContents) 'test each word in cell For m = 1 To o If InStr(1, Mid(strContents, intArray(m - 1), intArray(m) - intArray(m - 1) + 1), strFind) > 0 Then dblCount = dblCount + 1 o = o + 1 End If Next m Else 'only one word in cell If InStr(1, strContents, strFind) > 0 Then dblCount = dblCount + 1 End If End If Next 'return number of matches found xtndCount = dblCount Exit Function ' 'error handler ErrHand: xtndCount = CVErr(xlErrNA) End Function Note that the text from Public Function to As Double is all on one line. This function only has rudimentary error handling. Regards

Why are you entering multiple names in one cell and then trying to count them? Why not just enter them in separate cells, either in columns or rows? There is nothing wrong with using a User Defined Functions (UDF) such as Humar has offered, but if they were in separate cells you could use built in functions to manipulate your data. Right now, all you want to do is count the names, so Humar's suggestion might work for you. However, later on you might want to average them or compare the types of movies you attended with which person or even just sort on a name. By placing the list names in one cell, you are limited to what you can do with the data, unless you plan on writing a UDF for each operation.

Hi DerbyDad03 is right. It is always better to put data into individual cells. My solution is OK for where you already have a lot of data with multiple values in one cell and don't want to change it, or if someone else is sending you data with lots of values in one cell. In your case it is probably worth starting a new worksheet with a better layout. How about putting each person's name along the columns in row 1 and putting film names in the rows on column A. Then put 1 in the cell that intersects film name and person's name. New films can be added as time goes by, and new people can be added. If you may be watching one film more than once you have two choices either add it as a new row, or instead of a 1 enter the number of times you have watched the film with the same person. Then, as DerbyDad03 says, you will be able to do other things with the data, not just a simple addition. Regards