IF(AND)Etc.....
 

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

IF(AND)Etc.....
 

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

IF(AND)Etc.....
 

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



"Gary Brown" wrote:

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

IF(AND)Etc.....
 

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!

"Gary Brown" wrote:

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