Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003: Count how many times a date occurs
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003: Count how many times a date occurs
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) -- Biff Microsoft Excel MVP "lilhoot" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003: Count how many times a date occurs
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. On Jan 21, 3:08*pm, lilhoot wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003: Count how many times a date occurs
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? "Reitanos" wrote: 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. On Jan 21, 3:08 pm, lilhoot wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003: Count how many times a date occurs
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),RO WS(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. -- Biff Microsoft Excel MVP "lilhoot" wrote in message ... 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? "Reitanos" wrote: 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. On Jan 21, 3:08 pm, lilhoot wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count number of times a text occurs | New Users to Excel | |||
Need to Count the number of times a value occurs within a dt range | Excel Worksheet Functions | |||
Count the number of times a name occurs in a column | Excel Worksheet Functions | |||
I would like to count the # of times a value occurs in Col B base | Excel Worksheet Functions | |||
How to count the number of times something occurs within a certain month | Excel Worksheet Functions |