You need to have a Year Column in your 'In Progress' worksheet and your
'Closed
Problems' worksheet.
Then you would use the sumproduct function.
Assume Column AR contains the year in your 'In Progress' and 'Closed
Problems' worksheets.
Assume the date in your summary worksheet is in Column D.

=SUMPRODUCT(--('In Progress'!$AQ$3:$AQ$4079=Month(D3)),--('In
Progress'!$AR$3:$AR$4079=Year(D3)) + SUMPRODUCT(--('Closed
Problems'!$AQ$3:$AQ$3996=Month(D3)),--('Closed
Problems'!$AR$3:$AR$3996=Year(D3)))

--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"NetMaster" wrote:

I have a monster spreadsheet. It collects data from IT Problems in
infrastructure.

I need to count the number of problems that occurred in Jan 2009.

Here is my current formula.

Column AQ has the Month opened. (1 thru 12)
D$5 has the month in question. (jan-09)(Feb-08), etc...

=COUNTIF('In Progress'!$AQ$3:$AQ$4079,MONTH(D$5))+COUNTIF('Clos ed
Problems'!$AQ$3:$AQ$3996,MONTH(D$5))


It has worked fine until 2009. Now I have Problems that span both 2008 and
2009. So the current formula still counts 2008. To show the New problems for
Jan-09 I have to test for 2009, so it ignores 2008 problems.

Column AR has the Year opened. (2008 thru 2009, etc...)

When I try the formula below I get zero.

=COUNTIF('In Progress'!$AQ$3:$AQ$4079,MONTH(D$5))+COUNTIF('In
Progress'!$AR$3:$AR$4079,YEAR(D$5))+COUNTIF('Close d
Problems'!$AQ$3:$AQ$3996,MONTH(D$5))+COUNTIF('Clos ed
Problems'!$AR$3:$AR$3996,YEAR(D$5))

Can I doo something like this?

=IF(AND(AJ9:AJ14="1",AK9:AK14="2009"),COUNT(AI9:AI 14),"No Value")

Thanks in advanced.

Bob