CountIf on Date / Time

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