View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Pete_UK
 
Posts: n/a
Default Counting reoccuring dates.

Assume your worksheet is called "Data". Insert 3 blank worksheets.

Highlight the ReveivingDate column, click <copy, move to one of the
blank sheets, select cell A1 and press Enter. Then use Data | Filter |
Autofilter and select Unique Records and Move to Other Location
(specify $C$1) and click OK - this will give you a list of unique
dates, and you can delete columns A and B. You might like to sort the
dates at this point.

Enter this formula in B2 (you should have the heading ReceivingDate in
A1):

=COUNTIF(Data!A$2:A$1000,A2)

This assumes that the ReceivingDate column is A and that you have 1000
records in the Data sheet. Copy this formula down by double-clicking
the fill handle (the small black square in the bottom right corner of
the cursor).

You can repeat this procedure for the other columns in your main Data,
i.e. Type and Customer, copying each to a blank sheet and obtaining a
unique list then entering the COUNTIF formula with the appropriate
ranges of the Data sheet.

Hope this helps.

Pete