To Bob ( count question )

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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 Filter>Advanced 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

 

[Bob Phillips]

If this will be on another sheet, use

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

should have mentioned that