Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LEFT function-all to left of a comma? | Excel Worksheet Functions | |||
when inserting new worksheets they read right to left not left to. | Setting up and Configuration of Excel | |||
Column labels run right to left, not left to right | Excel Discussion (Misc queries) | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions | |||
How to change the right-to-left worksheet to left-to-right workshe | Excel Discussion (Misc queries) |