| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I Use A Count If Function With A Forward Slash?
I am trying to use a Count If function on a string of data with dates. The
data is downloaded like this -- Column A Row 1 7/7/2008 1:18:00 PM Row 2 7/7/2008 12:34:00 AM Row 3 7/8/2008 5:03:00 PM I tried setting up my Count If like this to count how many occurances there are of the date July 7, but it isn't working -- =COUNTIF(A:A,"7/7/2008*") Is it not working because of the forward slashes? I can change the format of the cells to show "Jul-07 x:xx:xx PM" but I cannot get the actual data to change to help me with the Count If function. Any ideas? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can I Use A Count If Function With A Forward Slash?
Are the entries TEXT or are they true Excel dates/times? They look like true
Excel dates/times. See if this works: =SUMPRODUCT(--(INT(A1:A10)=DATE(2008,7,7))) Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "mommy2kh" wrote in message ... I am trying to use a Count If function on a string of data with dates. The data is downloaded like this -- Column A Row 1 7/7/2008 1:18:00 PM Row 2 7/7/2008 12:34:00 AM Row 3 7/8/2008 5:03:00 PM I tried setting up my Count If like this to count how many occurances there are of the date July 7, but it isn't working -- =COUNTIF(A:A,"7/7/2008*") Is it not working because of the forward slashes? I can change the format of the cells to show "Jul-07 x:xx:xx PM" but I cannot get the actual data to change to help me with the Count If function. Any ideas? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can I Use A Count If Function With A Forward Slash?
Thanks for your reply. I couldn't get the following formula to work. I did
have to add in the spreadsheet since I am counting data from a different spreadsheet, so here is what I used (I have Excel 2003 if that makes a difference): =SUMPRODUCT(--(INT('Spreadsheet 2'!F1:F10000)=DATE(2008,7,7))) Did I put the spreadsheet reference in wrong or do you have any other ideas? I am getting #VALUE! in the cell. Thanks! "T. Valko" wrote: Are the entries TEXT or are they true Excel dates/times? They look like true Excel dates/times. See if this works: =SUMPRODUCT(--(INT(A1:A10)=DATE(2008,7,7))) Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "mommy2kh" wrote in message ... I am trying to use a Count If function on a string of data with dates. The data is downloaded like this -- Column A Row 1 7/7/2008 1:18:00 PM Row 2 7/7/2008 12:34:00 AM Row 3 7/8/2008 5:03:00 PM I tried setting up my Count If like this to count how many occurances there are of the date July 7, but it isn't working -- =COUNTIF(A:A,"7/7/2008*") Is it not working because of the forward slashes? I can change the format of the cells to show "Jul-07 x:xx:xx PM" but I cannot get the actual data to change to help me with the Count If function. Any ideas? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can I Use A Count If Function With A Forward Slash?
=SUMPRODUCT(--(INT('Spreadsheet 2'!F1:F10000)=DATE(2008,7,7)))
Did I put the spreadsheet reference in wrong or do you have any other ideas? I am getting #VALUE! in the cell. There's nothing wrong with that formula. Do you have any #VALUE! errors in the range? -- Biff Microsoft Excel MVP "mommy2kh" wrote in message ... Thanks for your reply. I couldn't get the following formula to work. I did have to add in the spreadsheet since I am counting data from a different spreadsheet, so here is what I used (I have Excel 2003 if that makes a difference): =SUMPRODUCT(--(INT('Spreadsheet 2'!F1:F10000)=DATE(2008,7,7))) Did I put the spreadsheet reference in wrong or do you have any other ideas? I am getting #VALUE! in the cell. Thanks! "T. Valko" wrote: Are the entries TEXT or are they true Excel dates/times? They look like true Excel dates/times. See if this works: =SUMPRODUCT(--(INT(A1:A10)=DATE(2008,7,7))) Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "mommy2kh" wrote in message ... I am trying to use a Count If function on a string of data with dates. The data is downloaded like this -- Column A Row 1 7/7/2008 1:18:00 PM Row 2 7/7/2008 12:34:00 AM Row 3 7/8/2008 5:03:00 PM I tried setting up my Count If like this to count how many occurances there are of the date July 7, but it isn't working -- =COUNTIF(A:A,"7/7/2008*") Is it not working because of the forward slashes? I can change the format of the cells to show "Jul-07 x:xx:xx PM" but I cannot get the actual data to change to help me with the Count If function. Any ideas? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can I Use A Count If Function With A Forward Slash?
P.S.
Another reason you'd get a #VALUE! error is if there are text entries in the range. Are you sure your dates/times are true Excel dates/times? If your range contains only these dates/times and they are in fact true Excel dates/times then this formula: =COUNT('Spreadsheet 2'!F1:F10000) Should return the number of entries in the range. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... =SUMPRODUCT(--(INT('Spreadsheet 2'!F1:F10000)=DATE(2008,7,7))) Did I put the spreadsheet reference in wrong or do you have any other ideas? I am getting #VALUE! in the cell. There's nothing wrong with that formula. Do you have any #VALUE! errors in the range? -- Biff Microsoft Excel MVP "mommy2kh" wrote in message ... Thanks for your reply. I couldn't get the following formula to work. I did have to add in the spreadsheet since I am counting data from a different spreadsheet, so here is what I used (I have Excel 2003 if that makes a difference): =SUMPRODUCT(--(INT('Spreadsheet 2'!F1:F10000)=DATE(2008,7,7))) Did I put the spreadsheet reference in wrong or do you have any other ideas? I am getting #VALUE! in the cell. Thanks! "T. Valko" wrote: Are the entries TEXT or are they true Excel dates/times? They look like true Excel dates/times. See if this works: =SUMPRODUCT(--(INT(A1:A10)=DATE(2008,7,7))) Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "mommy2kh" wrote in message ... I am trying to use a Count If function on a string of data with dates. The data is downloaded like this -- Column A Row 1 7/7/2008 1:18:00 PM Row 2 7/7/2008 12:34:00 AM Row 3 7/8/2008 5:03:00 PM I tried setting up my Count If like this to count how many occurances there are of the date July 7, but it isn't working -- =COUNTIF(A:A,"7/7/2008*") Is it not working because of the forward slashes? I can change the format of the cells to show "Jul-07 x:xx:xx PM" but I cannot get the actual data to change to help me with the Count If function. Any ideas? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can I Use A Count If Function With A Forward Slash?
Thanks! I tried the Count function and it did not return the correct number
of entries (it only counted the ones I am not having trouble with) so the data must be text which is why the formula didn't work. Is there a way to change it from text to Excel dates/times? "T. Valko" wrote: P.S. Another reason you'd get a #VALUE! error is if there are text entries in the range. Are you sure your dates/times are true Excel dates/times? If your range contains only these dates/times and they are in fact true Excel dates/times then this formula: =COUNT('Spreadsheet 2'!F1:F10000) Should return the number of entries in the range. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... =SUMPRODUCT(--(INT('Spreadsheet 2'!F1:F10000)=DATE(2008,7,7))) Did I put the spreadsheet reference in wrong or do you have any other ideas? I am getting #VALUE! in the cell. There's nothing wrong with that formula. Do you have any #VALUE! errors in the range? -- Biff Microsoft Excel MVP "mommy2kh" wrote in message ... Thanks for your reply. I couldn't get the following formula to work. I did have to add in the spreadsheet since I am counting data from a different spreadsheet, so here is what I used (I have Excel 2003 if that makes a difference): =SUMPRODUCT(--(INT('Spreadsheet 2'!F1:F10000)=DATE(2008,7,7))) Did I put the spreadsheet reference in wrong or do you have any other ideas? I am getting #VALUE! in the cell. Thanks! "T. Valko" wrote: Are the entries TEXT or are they true Excel dates/times? They look like true Excel dates/times. See if this works: =SUMPRODUCT(--(INT(A1:A10)=DATE(2008,7,7))) Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "mommy2kh" wrote in message ... I am trying to use a Count If function on a string of data with dates. The data is downloaded like this -- Column A Row 1 7/7/2008 1:18:00 PM Row 2 7/7/2008 12:34:00 AM Row 3 7/8/2008 5:03:00 PM I tried setting up my Count If like this to count how many occurances there are of the date July 7, but it isn't working -- =COUNTIF(A:A,"7/7/2008*") Is it not working because of the forward slashes? I can change the format of the cells to show "Jul-07 x:xx:xx PM" but I cannot get the actual data to change to help me with the Count If function. Any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Slash Zero | Excel Discussion (Misc queries) | |||
| Can I indicate zero with a slash? | Excel Discussion (Misc queries) | |||
| formulae for adding a forward slash automatically into a cell | New Users to Excel | |||
| Tab function sets me forward one page instead of one row | Excel Discussion (Misc queries) | |||
| Zero with a slash | Excel Discussion (Misc queries) | |||
Hybrid Mode
