Excel count cells

Pataman

Hi Interwebbers. I am looking for help please.
i have a spreadsheet, in column 3, I have a list of about 1500 entries. This is added to weekly. Its a list of names of about 80 people repeated according to the amount deliveries they have done. To see the current highest 10 deliverers, I sort by name and then highlight and it tells me the highest.
Is there a way to automate it? I can have a spare column with the people listed according to the amount of deliveries they have done.

Thanks

Taltos

You could simply create a pivot, change the range to be whole columns and just refresh that - all new additions and removals will be captured as soon as you hit refresh data.
Or do a countif on each name, it all depends on how you have your data arranged.

Pataman

Great...... whats a pivot?

Taltos

A pivot is a powerful tool that lets you do fast analysis on ranges of data.
However, many folk find them cumbersome to begin with in no small part to how MS designed them.

If you have a sample of data - even wrong values - just keep the column names we can do up a quick one as an example if you want.

Pataman

Thanks for your replies.
I have created a pivot, however that just lists the riders. I also need to know how many deliveries they did ie how many times they were entered into the worksheet

Pataman

found this pic: This would be similar.
If you look at column 3: I want to know how many times vegtables were delivered, how many fruit etc. A "top 10" if you know what I mean.

Slutmonkey57b

Which version of excel are you using?

Pataman

Slutmonkey57b wrote: »

Which version of excel are you using?

2007

Taltos

You can pull
Col C into the columns of the pivot
And say Col C again into Values as a COUNT

That should do it for you.
Somewhere there should be a sort option to show descending on count.

davetherave

I got a list of 100 names and ran a randomised it 2000 times.
See the first image.

By the way you are describing it I would say the second image is what you are looking for.
If you have managed to get a pivot done with the list of names then you just need to drag the column label down to the values are in the toolbar.

This will add a second column next to the names with the count of their occurrences.
You can then right click on any number in that column and sort, sort by largest to smallest to get sorted list.

Pataman

You sir are a genius. Thats exactly what I was looking for.
That should save me some work in future.
Thanks to all contributors for your help