Using SUMPRODUCT and Adding a Month Criteria

[stuu3270]

Hello again,

I currently have a spreadsheet I am working on, which I have addapted from something somebody sent me. The slightly different format of it (it uses a UserForm) is that there are references to an ADMIN worksheet that holds the data to be populated according to what is required (i.e. adding toggle buttons or column data by entering a value in a cell. This is apparent in the formula to follow.

I want to have a worksheet for each month of the year that summarises, from the Correspondence worksheet, what people have done. Here is what I have so far that works:
=SUMPRODUCT(--(Correspondence!$B$36:$B$999=ADMIN!$B$15),--(Correspondence!$C$36:$C$999=Correspondence!$A$8)).

Admin!B:B is a list of names for inclusion on a userform and spreadsheet cells.
Correspondence!A8:A31 is a list of correspondence types.

From row 36...
Column A contains dates entered as dd/mm/yyyy, displayed as dd-mmmm-yyyyColumn B is a person's name, displayed as text Column C contains a type of correspondence submitted by the person on the date shown.

The formula above works to count the rows with a person and a type of correspondence, however, I would like to add to this a criteria that only counts the data if within a certain month of the year.

I cannot submit the file as it contains sensitive data but hopefully what i have provided will be a good start.

Thanks,
Stu

 

[martindwilson]

=SUMPRODUCT(--(Correspondence!$B$36:$B$999=admin!$B$15),--(Correspondence!$C$36:$C$999=Correspondence!A8),--(MONTH(Correspondence!A36:A999)=2)) for feb
cross post at
http://www.excelforum.com/excel-for...oduct-for-two-criteria-and-month-of-year.html

 

[stuu3270]

Martin,

Works fine on my laptop at home with an up to date version of EXCEL. I will try it at work with the steam driven 2003 version but hopefully it's just what I was looking for. I had tried loads of things but probably only had just one little thing slightly off!

Thanks

Stu