Can I Use A Count If Function With A Forward Slash?

[mommy2kh]

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?

 

[T. Valko]

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.

 

[mommy2kh]

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]

=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?

 

[T. Valko]

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.

 

[mommy2kh]

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]

It depends...

Try this...

Select the range of cells in question
Goto the menu Data>Text 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

 

[mommy2kh]

I got it!!!!! Thanks so much for all your help -- you made my day!!!

 

[T. Valko]

You're welcome. Thanks for the feedback!