COUNTIF .... monthly

[Artorius]

I have a regular report that I need to produce and would like to set up a template so I drop raw data in and the report automates the results.

Here is the data:-

Contract End Date: dd/mm/yy

Report output:-

September 06: Number of Contracts with End date of 01/09/06 to 30/09/06

October 06: Number of contracts with end date of 01/10/06 to 30/10/06

etc etc

What is the best way to do this? The minds gone blank ........

Assuming Column A has the End Date ....

 

[texasalynn]

try this to get the results for September 2006
=SUMPRODUCT((MONTH(A2:A41)=9)*(YEAR(A2:A41)=2006))

 

[Artorius]

Works an absolute dream. I simply just changed the month and year ...

Although, I had Sept 06, Oct 06, Nov 06 in another column and tried amending the formula slightly to save me changing the Month and year to:-

=SUMPRODUCT((MONTH(A2:A41)=(MONTH(E2))*(YEAR(A2:A41)=(YEAR(E2)))

I was surprised that this did not work where E2 = "Sep 06". I'll just double check the cell is formatted as a date

But anyway ... your Formula worked superbly and a real time saver. Thanks.

 

[barry houdini]

If E2 contains any date within the month in question then this should work

=SUMPRODUCT((MONTH(A2:A41)=MONTH(E2))*(YEAR(A2:A41)=YEAR(E2)))

but if E2 contains the 1st of the month in question (however formatted) then this would also work

=SUMPRODUCT((A2:A41-DAY(A2:A41)+1=E2)+0)

 

[Artorius]

Works a treat. Thanks.