Formula to count number of text occurences in a range

If what you want is simply to count the number of occurrences of say "Tom", COUNTIF would suffice:

=COUNTIF(A1:A10,"Tom")

Aladin

of course. thanks. EOM

VB Macro code

Sub Find_Names()
'Finds all the unique names, count the number of times each name is listed and builds a sorted list with counts.
'Data is on Sheet2: Names(G), Values(I), Results are listed on Sheet3: Names(H), Count(I).
Application.ScreenUpdating = False

'Find unique names on Sheet2(G), col(7) & list on Sheet3(H).
With Intersect(Columns(7), ActiveSheet.UsedRange)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("Sheet3").Range("H1")
ActiveSheet.ShowAllData
End With

'Sort unique names on sheet3(H).
Sheets("Sheet3").Select
Columns(8).Sort Key1:=Range("H1")

'Count occurrences of names on Sheet2(G), col(7).
'List the number of times each unique name occurred on Sheet2(G), next to the list on sheet3(I).
With Worksheets("Sheet3")
.Range("I1").Formula = "=CountIf(Sheet2!" & Intersect(Sheet2.Columns(7), Sheet2.UsedRange).Address & ",H1)"
.Range("I1:I" & .Range("H1").End(xlDown).Row).FillDown
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

This code works with a form button or hot-key. JSW

A1:A10 each contain one of three words: "Tom", "Dick" or "Harry". Need a formula in B1 which will return number of times "Tom" appears in the range. The "Count" functions seem to deal with counting numbers/blank cells. Any thoughts?