Results 1 to 7 of 7

20021125, 09:02 #1
 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

20021125, 09:08 #2
 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.

20021125, 09:14 #3
 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

20021125, 09:17 #4
 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 OFC10.

20021125, 09:29 #5
 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

20021125, 10:17 #6
 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.

20021125, 10:25 #7
 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