If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SumProduct with Date Range and One Condition that is Text
Hello to all of you Excel Experts,
HELP, pretty please. I have 2 formulas I am trying to combine. I had to break them out, to try and troubleshoot my initial error, #Name? Both of these formulas worked, but I need them combined. I am pulling from a different worksheet and working in Excel 2003. I am trying to identify in one column (Column A) the site visits along with another column (Column E) looking for a date range of 09/01/08 - 09/30/08. The issue is I have to do a search because for some reason the names in Column A (Site Visits) were not picking up when I had the formula combined. =SUMPRODUCT(--(ISNUMBER(SEARCH('Project KPI Detail'!A15:A35,"Construction Start")))) =SUMPRODUCT(--(YEAR('Project KPI Detail'!$E$16:$E$37)=2008),--(MONTH('Project KPI Detail'!$E$16:$E$37)=9)) Thank you, The Novice |
#2
|
|||
|
|||
SumProduct with Date Range and One Condition that is Text
Make sure all your ranges have the size
"Iona" wrote: Hello to all of you Excel Experts, HELP, pretty please. I have 2 formulas I am trying to combine. I had to break them out, to try and troubleshoot my initial error, #Name? Both of these formulas worked, but I need them combined. I am pulling from a different worksheet and working in Excel 2003. I am trying to identify in one column (Column A) the site visits along with another column (Column E) looking for a date range of 09/01/08 - 09/30/08. The issue is I have to do a search because for some reason the names in Column A (Site Visits) were not picking up when I had the formula combined. =SUMPRODUCT(--(ISNUMBER(SEARCH('Project KPI Detail'!A15:A35,"Construction Start")))) =SUMPRODUCT(--(YEAR('Project KPI Detail'!$E$16:$E$37)=2008),--(MONTH('Project KPI Detail'!$E$16:$E$37)=9)) Thank you, The Novice |
#3
|
|||
|
|||
SumProduct with Date Range and One Condition that is Text
Make sure all your ranges have the same size
"Teethless mama" wrote: Make sure all your ranges have the size "Iona" wrote: Hello to all of you Excel Experts, HELP, pretty please. I have 2 formulas I am trying to combine. I had to break them out, to try and troubleshoot my initial error, #Name? Both of these formulas worked, but I need them combined. I am pulling from a different worksheet and working in Excel 2003. I am trying to identify in one column (Column A) the site visits along with another column (Column E) looking for a date range of 09/01/08 - 09/30/08. The issue is I have to do a search because for some reason the names in Column A (Site Visits) were not picking up when I had the formula combined. =SUMPRODUCT(--(ISNUMBER(SEARCH('Project KPI Detail'!A15:A35,"Construction Start")))) =SUMPRODUCT(--(YEAR('Project KPI Detail'!$E$16:$E$37)=2008),--(MONTH('Project KPI Detail'!$E$16:$E$37)=9)) Thank you, The Novice |
#4
|
|||
|
|||
SumProduct with Date Range and One Condition that is Text - Urgent
Thanks for the Range check. However, I am still experiencing issues combing
the 2 formulas below as one. Would you be able to assist me on the structure to make it one formula? I appreciate any help. Also, this is now due this morning. =SUMPRODUCT(--(ISNUMBER(SEARCH('Project KPI Detail'!A16:A37,"Construction Start")))) =SUMPRODUCT(--(YEAR('Project KPI Detail'!$E$16:$E$37)=2008),--(MONTH('Project KPI Detail'!$E$16:$E$37)=9)) "Teethless mama" wrote: Make sure all your ranges have the same size "Teethless mama" wrote: Make sure all your ranges have the size "Iona" wrote: Hello to all of you Excel Experts, HELP, pretty please. I have 2 formulas I am trying to combine. I had to break them out, to try and troubleshoot my initial error, #Name? Both of these formulas worked, but I need them combined. I am pulling from a different worksheet and working in Excel 2003. I am trying to identify in one column (Column A) the site visits along with another column (Column E) looking for a date range of 09/01/08 - 09/30/08. The issue is I have to do a search because for some reason the names in Column A (Site Visits) were not picking up when I had the formula combined. =SUMPRODUCT(--(ISNUMBER(SEARCH('Project KPI Detail'!A15:A35,"Construction Start")))) =SUMPRODUCT(--(YEAR('Project KPI Detail'!$E$16:$E$37)=2008),--(MONTH('Project KPI Detail'!$E$16:$E$37)=9)) Thank you, The Novice |
#5
|
|||
|
|||
SumProduct with Date Range and One Condition that is Text - Urgent
In the SEARCH function I think you have the arguments backwards.
SEARCH syntax (in plain English): SEARCH("search for what",search where,[optional: start search at character number]) Try this: =SUMPRODUCT(--(ISNUMBER(SEARCH("Construction Start",'Project KPI Detail'!A16:A37))),--(YEAR('Project KPI Detail'!$E$16:$E$37)=2008),--(MONTH('Project KPI Detail'!$E$16:$E$37)=9)) -- Biff Microsoft Excel MVP "Iona" wrote in message ... Thanks for the Range check. However, I am still experiencing issues combing the 2 formulas below as one. Would you be able to assist me on the structure to make it one formula? I appreciate any help. Also, this is now due this morning. =SUMPRODUCT(--(ISNUMBER(SEARCH('Project KPI Detail'!A16:A37,"Construction Start")))) =SUMPRODUCT(--(YEAR('Project KPI Detail'!$E$16:$E$37)=2008),--(MONTH('Project KPI Detail'!$E$16:$E$37)=9)) "Teethless mama" wrote: Make sure all your ranges have the same size "Teethless mama" wrote: Make sure all your ranges have the size "Iona" wrote: Hello to all of you Excel Experts, HELP, pretty please. I have 2 formulas I am trying to combine. I had to break them out, to try and troubleshoot my initial error, #Name? Both of these formulas worked, but I need them combined. I am pulling from a different worksheet and working in Excel 2003. I am trying to identify in one column (Column A) the site visits along with another column (Column E) looking for a date range of 09/01/08 - 09/30/08. The issue is I have to do a search because for some reason the names in Column A (Site Visits) were not picking up when I had the formula combined. =SUMPRODUCT(--(ISNUMBER(SEARCH('Project KPI Detail'!A15:A35,"Construction Start")))) =SUMPRODUCT(--(YEAR('Project KPI Detail'!$E$16:$E$37)=2008),--(MONTH('Project KPI Detail'!$E$16:$E$37)=9)) Thank you, The Novice |
Thread Tools | |
Display Modes | |
|
|