How do I count occurences in a date range?

[Guest]

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.

 

[RagDyeR]

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


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]

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

=SUMPRODUCT(--(MONTH(A1:A50)=1),--(A1:A50>0))

Is one way around it.