IF(AND)Etc.....

[NetMaster]

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('Closed
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('Closed
Problems'!$AQ$3:$AQ$3996,MONTH(D$5))+COUNTIF('Closed
Problems'!$AR$3:$AR$3996,YEAR(D$5))

Can I doo something like this?

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

Thanks in advanced.

Bob

 

[Gary Brown]

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)))

 

[NetMaster]

You Rule dude! Thanks a Million! here is the final formula. Now just 200
more formulas to modify with what I just learned. lol

=SUMPRODUCT(--('In Progress'!$AQ$3:$AQ$4079=MONTH(C$5)))--('In
Progress'!$AR$3:$AR$4079=YEAR(C$5))+SUMPRODUCT(--('Closed
Problems'!$AQ$3:$AQ$3996=MONTH(C$5)),--('Closed
Problems'!$AR$3:$AR$3996=YEAR(C$5))+SUMPRODUCT(--('Suggested
Problems'!$AQ$3:$AQ$3996=MONTH(C$5)),--('Suggested
Problems'!$AR$3:$AR$3996=YEAR(C$5)))+SUMPRODUCT(--('Deferred
Problems'!$AQ$3:$AQ$3996=MONTH(C$5)),--('Deferred
Problems'!$AR$3:$AR$3996=YEAR(C$5))))

 

[NetMaster]

Gary,

After reviewing my reults it appears that each calucation is off by 1. using
arrays in programming I always had to initialize the index to 0. it is
possible it was started at 1 already? When I manually count the number of In
Progress problems there are only 10. the formula comes up with 11.
I cant seem to find the source of the additional 1.

thanks for your help!