Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|