Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count different dates (Excel 2000)

    I have a column of dates and I would like to be able to calculate how many different dates there are in the column. I can count how many of each date there are but not how many dates there are!! Help !!! I would like there to be a single value rather than having to insert a new column.

    Paul

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count different dates (Excel 2000)

    Paul

    Two methods

    You could filter for unique values and then use subtotal =subtotal (2,range)

    You could use a pivot table (which more than uses an extra column <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    After that, all the methods I have use another column.

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count different dates (Excel 2000)

    Andrew,
    Thanks for the prompt reply. May be I was being to restructive in my original request!! What are the options with the extra column(s) ?

    Paul

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count different dates (Excel 2000)

    Say your dates are in A1:A100.

    The following formula, entered as an array formula (confirm with Ctrl+Shift+Enter), will return the number of unique dates in this range:

    =SUM(1/COUNTIF(A1:A100,A1:A100))

    See Using Array Formulas in Excel OFC-10.

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count different dates (Excel 2000)

    Hans,
    Works a treat!! Many thanks. Could you give me some hints about HOW it works ?

    Paul

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count different dates (Excel 2000)

    Paul,

    You can find the explanation - and a lot more - in the link (in my previous reply) to the paper on using array formulas by Bob Umlas.

    Here is a "simple" (ahem) example: say that A1 to A6 contain the numbers 6, 7, 8, 7, 6, 6
    In another cell, say B1, enter the array formula =SUM(1/COUNTIF(A1:A6,A1:A6)). Its value will be 3.
    Now, with B1 selected, click in the formula bar.
    Select COUNTIF(A1:A6,A1:A6) and evaluate it by pressing F9. (If you make a mistake, cancel the edit by pressing Esc and start anew).
    Ordinarily, COUNTIF is used with a range as first argument, and a single condition as second argument. It tests every cell in the first argument for the condition, and counts the number of cells that satisfy the condition. Here, the second argument is a range too, so the formula returns a range - a count for each element of the second argument. For instance, the first value returned will be 3, since 6 occurs three times.
    So, COUNTIF(A1:A6,A1:A6) will be replaced by {3,2,1,2,3,3} indicating that 6 occurs 3 times, 7 occurs 2 times, 8 occurs 1 time, 7 occurs 2 times, 6 occurs 3 times and 6 occurs 3 times.
    Now, select 1/{3,2,1,2,3,3} and evaluate it by pressing F9.
    You'll get {1/3,1/2,1,1/2,1/3,1/3} expressed as decimal numbers. As you see, each number that occurs once counts as 1 here, each number that occurs twice counts as 1/2 and each number that occurs 3 times counts as 1/3.
    So if we add this, each unique number will count as 1 (1/2+1/2=1 and 1/3+1/3+1/3=1).
    Are you still with me? I always get <img src=/S/confused.gif border=0 alt=confused width=15 height=20> thinking about array formulas.

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count different dates (Excel 2000)

    Hans!! It is an honour to be in the presence of you wonderful Longers!!! Thanks you very much for the answer and even more for taking the time to explain.
    Thanks to all.

    Paul

Posting Permissions

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