|
| 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
October 2nd, 2009, 03:04 PM
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
October 2nd, 2009, 03:14 PM
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
October 2nd, 2009, 03:34 PM
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
October 2nd, 2009, 05:27 PM
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
