Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Slash Zero Dick R. Excel Discussion (Misc queries) 4 May 24th 08 01:01 AM
Can I indicate zero with a slash? Laurie Excel Discussion (Misc queries) 3 February 9th 08 09:02 PM
formulae for adding a forward slash automatically into a cell angelgrant New Users to Excel 4 June 21st 06 01:32 PM
Tab function sets me forward one page instead of one row [email protected] Excel Discussion (Misc queries) 2 January 3rd 06 12:16 AM
Zero with a slash Stan Excel Discussion (Misc queries) 7 June 27th 05 08:10 PM


All times are GMT +1. The time now is 04:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"