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

Hi again Nigel,

Something like

=COUNTIF(B4:B5000,"Nigel")

You could put the name in another cell and refer to that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nigel" wrote in message
...
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

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

If this will be on another sheet, use

=COUNTIF('Sheet Name'!B4:B500,"Nigel"

should have mentioned that

--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...
Hi again Nigel,

Something like

=COUNTIF(B4:B5000,"Nigel")

You could put the name in another cell and refer to that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nigel" wrote in message
...
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