Since you cannot change the sheet with all the data, copy column B & paste it
into a clean sheet. Now, with any cell in the pasted data selected, use the
Data menu and select FilterAdvanced Filter

In the dialog box,

1) check 'Copy to Another Location'
2) specify another column to 'Copy to'
3) check 'Unique records only'
4) click on OK

This gives you a list of unique client names

Next to each unique name use the COUNTIF() function

=COUNTIF(pasted_data_range,cell with name)

Copy the formula down


"Nigel" wrote:

Thank you for all of your help Bob. I would like to ask you another question
though.
I have a long list in (range B4:B500). in this list are all of my Clients. (
about 50 clients in total ). the list is created by enquiry which means the
client names are entered at random. I now need to calculate how many
enquiries from each individual client per year. i have a large sheet set up
and cannot change the format of this. the information is to be entered onto
anther sheet in the same book.
summary:
count the amount of times each individual clients name appears in this long
list.

Kindest Regards,

Nigel