Thread: Count different dates (Excel 2000)

1. 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. 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. 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. 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. Re: Count different dates (Excel 2000)

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

Paul

6. 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. 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
•