adding a count

[sally]

On a file I have a list of client ids like the following:
clientID
111111
111111
111111
222222
222222
333333
444444
444444
444444
444444
What I would like is a fomula that will count each set of
client ids so it will look like this:
clientID Count
111111 1
111111 2
111111 3
222222 1
222222 2
333333 1
444444 1
444444 2
444444 3
444444 4

Please somebody help me I am at my whits end!!
thanks

 

[Frank Kabel]

Hi
have a look at pivot tables which will do this for you
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

For a formula approach you may use COUNTIF:
=COUNTIF(A1:A1000,111111)

 

[Paul Falla]

Dear Sally

The easiest way to do this is to use the sub-total
function. Open the Data menu, select subtotals. When
prompted select 'Count' from the drop-down box.

Hope this helps

Paul Falla

 

[Bob Phillips]

Sally,

Put this formula in B1 and copy down

=COUNTIF($A$1:A1,A1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)