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

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

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

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

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