M
mp80237
I am using Excel 2007. I have it looking at dates and a couple requirments
in the formula. N$1 is my date of 01/03/2008. The change details tab lists
all of the records. When I use this formula for a monthly report (using date
above January, 2008), it pulls the data correctly. But I need it for daily
activity. Below is my formula.
=SUMPRODUCT(--(Change_Details!$A$2:$A$1499-DAY(Change_Details!$A$2:$A$1499)+1=DATE(YEAR(N$1),MONTH(N$1),1)),--(ISNUMBER(SEARCH($L$3,Change_Details!$Q$2:$Q$1499)+(SEARCH($L$1,Change_Details!$B$2:$B$1499)))))
For example it came back as 3 records for the month of January. When I put
01/03/2008 into cell N$1 and change my formula (see below), it doesn’t work.
Below is the changed formula.
=SUMPRODUCT(--(Change_Details!$A$2:$A$1499-DAY(Change_Details!$A$2:$A$1499)+1=DATE(YEAR(N$1),MONTH(N$1),DAY(N$1))),--(ISNUMBER(SEARCH($L$3,Change_Details!$Q$2:$Q$1499)+(SEARCH($L$1,Change_Details!$B$2:$B$1499)))))
Am I getting something wrong? I comes back as 0 records
in the formula. N$1 is my date of 01/03/2008. The change details tab lists
all of the records. When I use this formula for a monthly report (using date
above January, 2008), it pulls the data correctly. But I need it for daily
activity. Below is my formula.
=SUMPRODUCT(--(Change_Details!$A$2:$A$1499-DAY(Change_Details!$A$2:$A$1499)+1=DATE(YEAR(N$1),MONTH(N$1),1)),--(ISNUMBER(SEARCH($L$3,Change_Details!$Q$2:$Q$1499)+(SEARCH($L$1,Change_Details!$B$2:$B$1499)))))
For example it came back as 3 records for the month of January. When I put
01/03/2008 into cell N$1 and change my formula (see below), it doesn’t work.
Below is the changed formula.
=SUMPRODUCT(--(Change_Details!$A$2:$A$1499-DAY(Change_Details!$A$2:$A$1499)+1=DATE(YEAR(N$1),MONTH(N$1),DAY(N$1))),--(ISNUMBER(SEARCH($L$3,Change_Details!$Q$2:$Q$1499)+(SEARCH($L$1,Change_Details!$B$2:$B$1499)))))
Am I getting something wrong? I comes back as 0 records