Excel 2003: Count how many times a date occurs

[lilhoot]

For example, if I work at a testing center and I want to know how many
students I tested on a that day, how would I count the date if my data is
laid out like this:
A B C
1 StudentName ID Date Tested
2 Joe A 123456 9/5/2006
3 Joe B 987654 10/8/2006
4 Joe C 123789 10/20/2006
5 Joe D 456123 10/20/2006
6 Joe E 789123 11/15/2006
7 Joe F 456789 12/5/2006
8 Joe G 159753 12/5/2006
9 Joe H 951753 12/5/2006

Thank you in advance for your help!

Tommy

 

[T. Valko]

Try one of these:

For the 12/5/2006 date:

=COUNTIF(C2:C8,DATE(2006,12,5))

Or, use a cell to hold the date:

E2 = 12/5/2006

=COUNTIF(C2:C8,E2)

 

[Reitanos]

As the previous responder said, COUNTIF may be just what you need.
Alternatively, if you use Autofilter, the SUBTOTAL function is great
when filtering.

To get the count from a subtotal you use: =SUBTOTAL(2,C2:C10000)
(The 2 makes it use count)

Subtotal will only count the displayed data, so that when the filter
is applied it only counts the data that is not hidden (eg selecting ID
123456 would then list and count only the classes that were taken by
Joe A) - this is more of a true database function at this point and
mimics the use of a view.

 

[lilhoot]

Thank you both for the help. I already know how many tests I have given. I
have given 733 students a test from 2005-2008. I am not targeting a specific
date. I just want to know which dates I gave more than 2 tests. Is this
possible?

 

[T. Valko]

This array formula** will list the dates that appear more than 2 times:

Assume you want the results to appear starting in cell E2.

=SMALL(IF(FREQUENCY(C$2:C$9,C$2:C$9)>2,C$2:C$9),ROWS(E$2:E2))

Copy down until you get #NUM! errors meaning all the data that meets the
conditon has been extracted.

If you want an error trap:

=IF(ROWS(E$2:E2)<=SUMPRODUCT(--(FREQUENCY(C$2:C$9,C$2:C$9)>2)),SMALL(IF(FREQUENCY(C$2:C$9,C$2:C$9)>2,C$2:C$9),ROWS(E$2:E2)),"")

Copy down until you get blanks.

Format as Date

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.