Counting Occurences In A Column

[Guest]

Hi

Here's one for you Excel forumla wizards:

Using Excel 2007 -

I have a list of customer names in Column C of a customer database I have
created, some names are repeated as they are frequent customers. Is there a
(hopefully simple) formula that would automatically search column C and tell
me how many times each customer has appeared in that column, thereby allowing
me to readily see which customers return the most?

Very many thanks.

 

[Guest]

You don't even need a formula for this one. Just a Pivot table.

See:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

for example if column A had:

Animal
mouse
mouse
cat
cat
cat
dog
cat
cat
cat
cat
dog
dog

the the pivot table would be:

Count of Animal
Animal Total
cat 7
dog 3
mouse 2
Grand Total 12

 

[Don Guillett]

Assuming your customer names in col F. One way is to use this in col G and
copied down.
=COUNTIF($F$2:$F$22,F2)
Sort or
Then use match to find the row in that column and find the name it applies
to.
=INDEX(F2:F22,MATCH(LARGE(G2:G22,1),G2:G22,0))

 

[Guest]

Hi Gary

Many thanks for the reply, but looking at the instructions for creating
pivot data, my brain exploded out of my ears - it looks very complicated.
All the instructions seem to be aimed at multiple columns of data. The
example you gave (animals) looks just what I am looking for, but I can't see
how it would be created.

 

[Guest]

Hi Don

Many thanks for your reply, and I appreciate your help, but I have to
confess - your reply made no sense to me whatsover. Sorry

 

[Don Guillett]

A simpler way. Make a list of your clients in col D and use this copied
down. Then sort.
=COUNTIF($F$2:$F$22,D2)

 

[Guest]

Let's walk thru it step-by-step:

1. highlight cells A1 thru A13 (my example)
2. pull-down:
Data > PivotTable > Next > Next > Layout

You will now see 4 panes: Page, Row, Column, and Data

Drag the Animal button into the Row pane and then also into the Data pane
(the Data pane will show: Count of Animal)
Then click OK
Then click Finish


Update this post if you experience any problems.

 

[Guest]

Hi Gary

You must be using a different version of Excel to me as there is no
"PivotTable" option under the data tab. The only place I can find such an
option is under the "Insert" tab, and then I do not get the next/next/layout
options you speak of. I get asked such questions as whether I want the data
on another spreadsheet, or the existing one.

I did state in my original post that I was using Outllok 2007. Is this the
version you are working from in your instructions?

 

[Guest]

Hi Don

You don't explain where I put the "=COUNTIF($F$2:$F$22,D2)" forumula!
What do you mean by "Copied Down"?

 

[Guest]

Hi Gary

You must be using a different version of Excel to me as there is no
"PivotTable" option under the data tab. The only place I can find such an
option is under the "Insert" tab, and then I do not get the next/next/layout
options you speak of. I get asked such questions as whether I want the data
on another spreadsheet, or the existing one.

I did state in my original post that I was using Excel 2007. Is this the
version you are working from in your instructions?

 

[Guest]

Further tio my last reply;

Where I stated: "I did state in my original post that I was using Outllok
2007. Is this the version you are working from in your instructions?"

I did mean Excel 2007. Sorry.

 

[Don Guillett]

Put the formula in any blank column that you desire. Then use the grab
handle and drag down. The grab handle can be had by going to the bottom
right of the cell with the formula where there is a small square. Grab this
with your mouse and drag down the column to the end of where your list of
names is. Got it?

 

[Guest]

Got it!

In 2007 there is no field called "Data". The four boxes are labelled
"Report Filter", "Row Labels", Column Labels", and "Values".

I tinkered with them going by your instructions and finally managed to
'translate' what you were syaing into 2007 language

Many thanks.

 

[Guest]

Hi Don

I do not know why, but I just couldn't get your formula to work. However, I
eventually got what I wanted by using the Pivot Tabel idea from 'Gary"s
Student'.

Many thanks anyway for taking the time and effort to help. It was much
appreciated.

Thank you.