How to count same words in different cells?

[wonderlust]

Hi,
I have the following problem:
+1300 rows of different names of series
+each cell contains a couple of names (e.g. A1: "Breaking Bad, Transformers, Harry Potter")
+some of the names are the same, but some are different (e.g. A2:"Harry Potter, LOTR, The Simpsons")

How can I count the number of repetative names? (e.g. "in the 1300 rows there are 200 mentions of "Harry Potter")?

 

[ExceLoki]

if you can split the movies using text to columns this will work
----------------------------------

Book1
	A	B	C	D	E	F
1	movies 1	movie 2	movie 3		movies	count
2	harry potter	LOTR	The Simpsons		harry potter	4
3	harry potter	breaking bad	transformers		LOTR	2
4	The Simpsons	LOTR	breaking bad		The Simpsons	3
5	breaking bad	a team	transformers		breaking bad	3
6	a team	harry potter	gi joe		transformers	2
7	harry potter	The Simpsons	a team		a team	3
8					gi joe	1
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=INDIRECT(TEXT(MIN(IF(($A$2:$C$7<>"")*(COUNTIF($E$1:E1,$A$2:$C$7)=0),ROW($2:$7)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
F2:F8	F2	=IF(E2="","",COUNTIF(A:C,E2))

 

[igold]

Another alternative:

Sub CountTitles()

    Dim i As Long, ct As Long, N As Long, a As Long, r As Long
    Dim arr, Nams, arr2
    Dim resp As String
    
    N = 1
    arr = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    For i = 1 To UBound(arr)
        Nams = Split(arr(i, 1), ",")
        ct = ct + UBound(Nams) + 1
    Next
    ReDim arr2(1 To ct, 1 To 1)
    i = 1
    For i = 1 To UBound(arr)
        Nams = Split(arr(i, 1), ",")
        For a = 0 To UBound(Nams)
            arr2(N, 1) = Trim(Nams(a))
            N = N + 1
        Next
    Next
    resp = InputBox("Please Enter the Title You Would Like to Count:")
    For i = LBound(arr2) To UBound(arr2)
        If Trim(UCase(arr2(i, 1))) = Trim(UCase(resp)) Then r = r + 1
    Next
    MsgBox "There are " & r & " instances of " & resp
    
End Sub