How do I count occurences in a date range?
 

I have a table of data of closed accounts. In that table, I have a column
including the cancellation date. I want to count the number of cases that
cancelled in Jan, Feb, etc. I've tried combinations of "countif", "and" and
<=, = in all my formulas but I keep getting 0 as my result.

One way:

=SUMPRODUCT(--(MONTH(A1:A50)=1))

Where the *number* of the month (Jan=1, Feb=2, ...etc.) is in the formula

OR

=SUMPRODUCT(--(MONTH(A1:A50)=C1))

Where the number of the month is entered into another cell, so that it can
be easily changed to count other months.

If your list is multi-year, you may want to add a specific year to further
classify the count:


=SUMPRODUCT((MONTH(A1:A50)=1)*(YEAR(A1:A50)=2005))

OR

=SUMPRODUCT((MONTH(A1:A50)=C1)*(YEAR(A1:A50)=C2))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Jeremy" wrote in message
...
I have a table of data of closed accounts. In that table, I have a column
including the cancellation date. I want to count the number of cases that
cancelled in Jan, Feb, etc. I've tried combinations of "countif", "and" and
<=, = in all my formulas but I keep getting 0 as my result.

That first one can give misleading results if there are empty cells in the
range.

=SUMPRODUCT(--(MONTH(A1:A50)=1),--(A1:A500))

Is one way around it.

RagDyeR wrote:

One way:

=SUMPRODUCT(--(MONTH(A1:A50)=1))

Where the *number* of the month (Jan=1, Feb=2, ...etc.) is in the formula

OR

=SUMPRODUCT(--(MONTH(A1:A50)=C1))

Where the number of the month is entered into another cell, so that it can
be easily changed to count other months.

If your list is multi-year, you may want to add a specific year to further
classify the count:

=SUMPRODUCT((MONTH(A1:A50)=1)*(YEAR(A1:A50)=2005))

OR

=SUMPRODUCT((MONTH(A1:A50)=C1)*(YEAR(A1:A50)=C2))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Jeremy" wrote in message
...
I have a table of data of closed accounts. In that table, I have a column
including the cancellation date. I want to count the number of cases that
cancelled in Jan, Feb, etc. I've tried combinations of "countif", "and" and
<=, = in all my formulas but I keep getting 0 as my result.

--

Dave Peterson