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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I Use A Count If Function With A Forward Slash?
It depends...
Try this... Select the range of cells in question Goto the menu DataText to Columns Click Finish This will usually convert text numbers back to numeric numbers. Also see this for additional info: http://contextures.com/xlDataEntry03.html -- Biff Microsoft Excel MVP "mommy2kh" wrote in message ... 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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I Use A Count If Function With A Forward Slash?
I got it!!!!! Thanks so much for all your help -- you made my day!!!
"T. Valko" wrote: It depends... Try this... Select the range of cells in question Goto the menu DataText to Columns Click Finish This will usually convert text numbers back to numeric numbers. Also see this for additional info: http://contextures.com/xlDataEntry03.html -- Biff Microsoft Excel MVP "mommy2kh" wrote in message ... 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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I Use A Count If Function With A Forward Slash?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "mommy2kh" wrote in message ... I got it!!!!! Thanks so much for all your help -- you made my day!!! "T. Valko" wrote: It depends... Try this... Select the range of cells in question Goto the menu DataText to Columns Click Finish This will usually convert text numbers back to numeric numbers. Also see this for additional info: http://contextures.com/xlDataEntry03.html -- Biff Microsoft Excel MVP "mommy2kh" wrote in message ... 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) |