How do I add LEFT to this array?
 

=SUMPRODUCT(--(ISERROR(SEARCH("Account Request",'Incident
Data'!$E$2:$E$5000))=FALSE),--(MONTH('Incident
Data'!$G$2:$G$5000)=MONTH(C$8)))

I want to modify it to restrict where it looks for "Account Request". I
figure add
LEFT('Incident Data'!$E$2:$E$5000,15) however that returns an error.
I also tried putting LEFT after SEARCH.

I am still very new to SUMPRoduct and I had help coming up with the formula
above; which works perfectly now.
I am also reading through:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I don't want to break my wonderful "array"? Can anyone help?

Sonya

How do I add LEFT to this array?
 

Try replacing the 1st term in your sumproduct with this equivalent:
--ISNUMBER(SEARCH("Account Request",LEFT('Incident Data'!$E$2:$E$5000,15)))
Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"TechieGirl" wrote:
=SUMPRODUCT(--(ISERROR(SEARCH("Account Request",'Incident
Data'!$E$2:$E$5000))=FALSE),--(MONTH('Incident
Data'!$G$2:$G$5000)=MONTH(C$8)))

I want to modify it to restrict where it looks for "Account Request". I
figure add
LEFT('Incident Data'!$E$2:$E$5000,15) however that returns an error.
I also tried putting LEFT after SEARCH.

I am still very new to SUMPRoduct and I had help coming up with the formula
above; which works perfectly now.
I am also reading through:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I don't want to break my wonderful "array"? Can anyone help?

Sonya

How do I add LEFT to this array?
 

It passed validation, its perfect!
:)

"Max" wrote:

Try replacing the 1st term in your sumproduct with this equivalent:
--ISNUMBER(SEARCH("Account Request",LEFT('Incident Data'!$E$2:$E$5000,15)))
Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"TechieGirl" wrote:
=SUMPRODUCT(--(ISERROR(SEARCH("Account Request",'Incident
Data'!$E$2:$E$5000))=FALSE),--(MONTH('Incident
Data'!$G$2:$G$5000)=MONTH(C$8)))

I want to modify it to restrict where it looks for "Account Request". I
figure add
LEFT('Incident Data'!$E$2:$E$5000,15) however that returns an error.
I also tried putting LEFT after SEARCH.

I am still very new to SUMPRoduct and I had help coming up with the formula
above; which works perfectly now.
I am also reading through:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I don't want to break my wonderful "array"? Can anyone help?

Sonya

How do I add LEFT to this array?
 

--ISNUMBER(SEARCH("Account Request",LEFT('Incident Data'!$E$2:$E$5000,15)))

Or:

--(LEFT('Incident Data'!$E$2:$E$5000,15)="Account Request")

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
Try replacing the 1st term in your sumproduct with this equivalent:
--ISNUMBER(SEARCH("Account Request",LEFT('Incident
Data'!$E$2:$E$5000,15)))
Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"TechieGirl" wrote:
=SUMPRODUCT(--(ISERROR(SEARCH("Account Request",'Incident
Data'!$E$2:$E$5000))=FALSE),--(MONTH('Incident
Data'!$G$2:$G$5000)=MONTH(C$8)))

I want to modify it to restrict where it looks for "Account Request". I
figure add
LEFT('Incident Data'!$E$2:$E$5000,15) however that returns an error.
I also tried putting LEFT after SEARCH.

I am still very new to SUMPRoduct and I had help coming up with the
formula
above; which works perfectly now.
I am also reading through:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I don't want to break my wonderful "array"? Can anyone help?

Sonya