To Bob ( count question )


G

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
 
Ad

Advertisements

B

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)
 
G

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
 
Ad

Advertisements

B

Bob Phillips

If this will be on another sheet, use

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

should have mentioned that
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top