Okay, so I have a new CountIf dilema. Will it work for a time field?

My date/time field looks like this: 5/6/2009 12:01:00 AM.

Is there any way to count the 12:00 - 12:59 entries? There could be
hundreds. All will be of the same date.

My range is A2:A10000.

Thanks for your help.

Hi,

Try this

=SUMPRODUCT((((VALUE(C5:C7)-(INT(VALUE(C5:C7)))=0.5))*(VALUE(C5:C7)-(INT(VALUE(C5:C7)))<=0.540972222224809)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RoadKill" wrote in message
...
Okay, so I have a new CountIf dilema. Will it work for a time field?

My date/time field looks like this: 5/6/2009 12:01:00 AM.

Is there any way to count the 12:00 - 12:59 entries? There could be
hundreds. All will be of the same date.

My range is A2:A10000.

Thanks for your help.

My date/time field looks like this: 5/6/2009 12:01:00 AM.

If the entries are true Excel date/time values *and* there are no empty
cells within the range:

=SUMPRODUCT(--(HOUR(A2:A10000)=0))

--
Biff
Microsoft Excel MVP


"RoadKill" wrote in message
...
Okay, so I have a new CountIf dilema. Will it work for a time field?

My date/time field looks like this: 5/6/2009 12:01:00 AM.

Is there any way to count the 12:00 - 12:59 entries? There could be
hundreds. All will be of the same date.

My range is A2:A10000.

Thanks for your help.

Give this a try...

=SUMPRODUCT(--(MOD(A7:A19,1)<0.041666))

--
Rick (MVP - Excel)


"RoadKill" wrote in message
...
Okay, so I have a new CountIf dilema. Will it work for a time field?

My date/time field looks like this: 5/6/2009 12:01:00 AM.

Is there any way to count the 12:00 - 12:59 entries? There could be
hundreds. All will be of the same date.

My range is A2:A10000.

Thanks for your help.

Interesting that all three approaches gave vastly different results, with
none of them being correct. I thank you all for the help but will go a
different route.

"RoadKill" wrote:

Okay, so I have a new CountIf dilema. Will it work for a time field?

My date/time field looks like this: 5/6/2009 12:01:00 AM.

Is there any way to count the 12:00 - 12:59 entries? There could be
hundreds. All will be of the same date.

My range is A2:A10000.

Thanks for your help.

If you can provide more detail maybe we can figure out what the problem is.
My first guess would be that your entries aren't true Excel date/times but
are instead TEXT strings.

--
Biff
Microsoft Excel MVP


"RoadKill" wrote in message
...
Interesting that all three approaches gave vastly different results, with
none of them being correct. I thank you all for the help but will go a
different route.

"RoadKill" wrote:

Okay, so I have a new CountIf dilema. Will it work for a time field?

My date/time field looks like this: 5/6/2009 12:01:00 AM.

Is there any way to count the 12:00 - 12:59 entries? There could be
hundreds. All will be of the same date.

My range is A2:A10000.

Thanks for your help.

Excel 2007

=SUMPRODUCT(--(HOUR(A2:A10000)=0))

I have a similar need as the original poster, except that I need to count up
entries by month. I modified the above command as follows:

=SUMPRODUCT(--(MONTH(Incident_Dates)=4))

That works to count all the entries for April. However, my Incident_Dates
range is 100 rows, and most of those do not yet contain data. If I use my
version of your formula and the range does not contain any dates for a given
month, the formula returns 100 minus the number of cells in the range that
do contain data. How could the number of entries for a given month be
counted without the blank cells skewing the results?

--Tom

Empty cells will evaluate as month 1 (January). So, if you're counting for
month 4 (April) the empty cells shouldn't make a difference. The only time
the empty cells will make a difference is if you're counting month 1.

To account for that just add a test that the cells contain a number or that
the cells are not blank.

=SUMPRODUCT(--(ISNUMBER(Incident_Dates)),--(MONTH(Incident_Dates)=4))

=SUMPRODUCT(--(Incident_Dates<""),--(MONTH(Incident_Dates)=4))


--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
Excel 2007

=SUMPRODUCT(--(HOUR(A2:A10000)=0))

I have a similar need as the original poster, except that I need to count
up entries by month. I modified the above command as follows:

=SUMPRODUCT(--(MONTH(Incident_Dates)=4))

That works to count all the entries for April. However, my Incident_Dates
range is 100 rows, and most of those do not yet contain data. If I use my
version of your formula and the range does not contain any dates for a
given month, the formula returns 100 minus the number of cells in the
range that do contain data. How could the number of entries for a given
month be counted without the blank cells skewing the results?

--Tom

"T. Valko" wrote...
My date/time field looks like this: 5/6/2009 12:01:00 AM.

If the entries are true Excel date/time values *and* there are no empty
cells within the range:

=SUMPRODUCT(--(HOUR(A2:A10000)=0))
....

So filter out the empty cells. BTW, HOUR returns the hour for text
that could be converted by TIMEVALUE, so it's only blank cells that
would be trouble.

Anyway, try

=SUMPRODUCT(--(TEXT(A2:A10000,"h;;;")="0"))

or

=SUMPRODUCT(--(HOUR(A2:A10000)-ISBLANK(A2:A10000)=0))

So filter out the empty cells.

Yep. Just didn't know how to do that from a syntax point of view as I am
not very familiar with the SUMPRODUCT function. I'll give your suggestions
a try. Thanks for the help.

--Tom

"Harlan Grove" wrote in message
...
"T. Valko" wrote...
My date/time field looks like this: 5/6/2009 12:01:00 AM.

If the entries are true Excel date/time values *and* there are no empty
cells within the range:

=SUMPRODUCT(--(HOUR(A2:A10000)=0))
...

So filter out the empty cells. BTW, HOUR returns the hour for text
that could be converted by TIMEVALUE, so it's only blank cells that
would be trouble.

Anyway, try

=SUMPRODUCT(--(TEXT(A2:A10000,"h;;;")="0"))

or

=SUMPRODUCT(--(HOUR(A2:A10000)-ISBLANK(A2:A10000)=0))

I used the first option and it that works perfectly.

In my particular situation I need to keep a counter for each month. So I
have the months listed in F1:Q1, I named that range, and then in F2:Q2 I
have the following modification of your formula:

=SUMPRODUCT(--(ISNUMBER(Incident_Dates)),--(MONTH(Incident_Dates)=MATCH(I1,Months_List,0)))

That gives me a formula that is the same for all 12 months (I don't have to
put =1, =2, =3, etc. at the end of each formula).

Thanks for the help.

--Tom

"T. Valko" wrote in message
...
Empty cells will evaluate as month 1 (January). So, if you're counting for
month 4 (April) the empty cells shouldn't make a difference. The only time
the empty cells will make a difference is if you're counting month 1.

To account for that just add a test that the cells contain a number or
that the cells are not blank.

=SUMPRODUCT(--(ISNUMBER(Incident_Dates)),--(MONTH(Incident_Dates)=4))

=SUMPRODUCT(--(Incident_Dates<""),--(MONTH(Incident_Dates)=4))


--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
Excel 2007

=SUMPRODUCT(--(HOUR(A2:A10000)=0))

I have a similar need as the original poster, except that I need to count
up entries by month. I modified the above command as follows:

=SUMPRODUCT(--(MONTH(Incident_Dates)=4))

That works to count all the entries for April. However, my
Incident_Dates range is 100 rows, and most of those do not yet contain
data. If I use my version of your formula and the range does not contain
any dates for a given month, the formula returns 100 minus the number of
cells in the range that do contain data. How could the number of entries
for a given month be counted without the blank cells skewing the results?

--Tom

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
I used the first option and it that works perfectly.

In my particular situation I need to keep a counter for each month. So I
have the months listed in F1:Q1, I named that range, and then in F2:Q2 I
have the following modification of your formula:

=SUMPRODUCT(--(ISNUMBER(Incident_Dates)),--(MONTH(Incident_Dates)=MATCH(I1,Months_List,0)))

That gives me a formula that is the same for all 12 months (I don't have
to put =1, =2, =3, etc. at the end of each formula).

Thanks for the help.

--Tom

"T. Valko" wrote in message
...
Empty cells will evaluate as month 1 (January). So, if you're counting
for month 4 (April) the empty cells shouldn't make a difference. The only
time the empty cells will make a difference is if you're counting month
1.

To account for that just add a test that the cells contain a number or
that the cells are not blank.

=SUMPRODUCT(--(ISNUMBER(Incident_Dates)),--(MONTH(Incident_Dates)=4))

=SUMPRODUCT(--(Incident_Dates<""),--(MONTH(Incident_Dates)=4))


--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
Excel 2007

=SUMPRODUCT(--(HOUR(A2:A10000)=0))

I have a similar need as the original poster, except that I need to
count up entries by month. I modified the above command as follows:

=SUMPRODUCT(--(MONTH(Incident_Dates)=4))

That works to count all the entries for April. However, my
Incident_Dates range is 100 rows, and most of those do not yet contain
data. If I use my version of your formula and the range does not
contain any dates for a given month, the formula returns 100 minus the
number of cells in the range that do contain data. How could the number
of entries for a given month be counted without the blank cells skewing
the results?

--Tom