Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2009
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Objective: Parse a large column of dates(6k+ lines) and output the number of occurrences for each unique date.

    I have tried using Countif and it doesn't provide the expected result as it counts each occurrence separately, ie,
    1/20/2009 1
    [highlight=yellow]1/21/2009 3
    1/21/2009 2
    1/21/2009 1[/highlight]
    1/22/2009 4
    1/22/2009 3
    1/22/2009 2
    1/22/2009 1

    Ideally I would like it to output a new column with each date only once and the count for each date.

    I am using MS Office Excel 2007 SP1

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If these are dates and all inclusive, you can list the dates and then use SUMIF not COUNTIF if you want to sum the other values not count the number of unique dates

    If you truly want to extract just unique items in the list in a more generic form, you can use a pivot table to put the dates in the "row" field and sum the other column in the "Data" field of the pivot layout.


    Steve

  3. #3
    New Lounger
    Join Date
    Apr 2009
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In the above example what you are seeing is the result of CountIF looking at each date and then count how many lines below have that date. What I need is a faster way to handle the data rather than manually counting the number of how many times the date 01/02/2009 shows up. I am building a Frequency Chart showing the date calls come in about certain software problems across our product line.

    I would like to run a formula against the range A1:A6081 with the result something like this:
    01/02/2009 5 'This date showed up five times in the data
    01/03/2009 16 'This date showed up sixteen times in the data

    I tried the pivot table, but I am obviously missing something since I received no data back.

    Sorry for not being clear in the beginning.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See the attached sample workbook (in Excel97-2003 format).

    There's a small data table, and a pivot table that counts the number of times each date occurs.
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Apr 2009
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks! Now I see the light, and the error of my ways!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •