Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Function
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 doesnt 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Function
The first part of your function is forcing the comparison date on the
Change_Details sheet to be the first of the month: Change_Details!$A$2:$A$1499-DAY(Change_Details!$A$2:$A$1499)+1 If you want to compare actual dates the formula gets a lot simpler, as you don't have to adjust the Change_Detail date. You also don't need to break N1 into components (year, month, day) just to put them back together: =SUMPRODUCT(--(Change_Details!$A$2:$A$1499=$N$1),--(ISNUMBER(SEARCH($L$3,Change_Details!$Q$2:$Q$1499) +(SEARCH($L$1,Change_Details!$B$2:$B$1499))))) (I'm assuming that N1 and the dates in Change_Details!A:A are JUST dates, with no time specified. If that's not true, you'll need to calculate away the time components) "mp80237" wrote: 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 doesnt 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Function
If N1 = 1/3/2008 .....
The only difference between the 2 formulas is that the first one is "looking for" 1/1/2008 and the second one is "looking for" 1/3/2008. -- Biff Microsoft Excel MVP "mp80237" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Function
More info:
The only difference between the 2 formulas is that the first one is "looking for" 1/1/2008 and the second one is "looking for" 1/3/2008.... Which will never meet the condition since you're manipulating the dates to be the first of the month. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... If N1 = 1/3/2008 ..... The only difference between the 2 formulas is that the first one is "looking for" 1/1/2008 and the second one is "looking for" 1/3/2008. -- Biff Microsoft Excel MVP "mp80237" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT or another function??? | Excel Worksheet Functions | |||
sumproduct function | Excel Worksheet Functions | |||
SumProduct function | Excel Worksheet Functions | |||
Sumproduct Function | Excel Discussion (Misc queries) | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions |