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
0
10/2/2009 2:04:02 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
462 Views

Similar Articles

[PageSpeed] 17

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
0
demechanik (4694)
10/2/2009 2:14:02 PM
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
0
10/2/2009 2:34:02 PM
>--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" <demechanik@yahoo.com> wrote in message 
news:44EA2155-82B7-4E19-A1DF-CBFE20C6879B@microsoft.com...
> 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 


0
biffinpitt (3172)
10/2/2009 4:27:15 PM
Reply:

Similar Artilces:

How can I add transaction while using the online version of money
Ok... when I first bought Money several years ago and installed it on my home computer, I could then login while at work via the passport and see the information that I had entered on my home computer. If I purchased something during the day I could go online and add the transaction which was great way to know at any given moment what my TRUE balance was in my account. Now when I check my Money account online from work I can see all the information I've entered at home but now there is no way to add a new transaction other then from my home computer where I have Money installed. ...

Excel 2003 adds extra quotes to CSV upon resaving
I have a CSV that when viewed in notepad has the following 2 lines "R1","J","3","","N","4",":4","0" "R1","L","2","","N","4",":4","0" but no matter if i open in by double-clicking or importing the data into a blank worksheet, then resaving, when i open the CSV in notepad, it comes out looking like this, with extra quotes, rather than just the quoting following along with the original 2 lines. """R1"","...

Problem in permanent type add-in toolbar
Hi, I have been facing a issue with dealing with my add-in toolbar. I want to make my toolbar permanent so that use can hide and reposition it permanently(even after restarting Outlook). At the same time, I want to support my add-in toolbar for all opened Outlook explorer. I want to make sure that at any point of time there must be one and only one toolbar on any Outlook window. I have tried below methods but none worked: 1. Tried deleting toolbar on shutdown. This doesn't delete the toolbar and each restarting outlook adds new toolbar. Private Sub AddinInstance_OnBegi...

VC++ .NET 2002: How To Use the Class View's Properties window to add a message handler for the WN_KEYDOWN message
Hi all I used "The EX05c Example: Scrolling" of the book "Programming With Microsoft Visual C++ .NET Core Reference" (6th Edition) as my 3rd example of MFC exercises to catch up my VC++ .NET 2002 (VC++ 7.0) programming. I started my 'Eg05c' project in the MFC Application Wizard and followed the 1 thru 7 steps instructed in the book (pages 82-85) I believed I did the steps 1, 2 and 3 correctly: 1 - In MFC Application Wizard -Eg05c, I setthe CEg05cView to "CScrollView" 2 - I added the m_rectEllipse and m_nColor data members in Eg05cView.h by typing in "...

Excel Add-In for SQL Server Analysis Services Error -- Need Help
I have the add-in installed and it was working. However, lately I keep getting an error stating: Excel Add-in for Analysis Services has not been initialized I have uninstalled and reinstalled but no luck. Anyone help out here? Alan ...

newbie question on multi-dimensional array
Hi all, I have a real lame question about multi-dimensional array operation. Suppose I have defined a two-dimensional array "Array(10,10)". If I want to use the Excel function "Average" to compute the average value of Column, say 10, of that 2D array, is there any way to realize it? I tried "Application.Average(Array(:,10))", but apparently the Excel doesn't like that. (You can see I am a Matlab guy, so please don't laugh at me:) ) Any comments are extremely welcome. -- sammus ------------------------------------------------------------------------...

How do I stop "Do you want to add this person to messenger" popups
Several times a week I'm getting popups, asking if I want to "add this person to messenger". This is getting very, very annoying. How do I turn this "feature" off? I know when someone I actually care about is going to add me to messenger, I can turn it on when that time comes. Several times a week? I get it many times a day, and it's driving me crazy! What you are supposed to do is go to Tools -> Options -> Privacy. Here, under Contact Lists you will find a check box saying "Alert me when other people add me to their contact list". Y...

add to Runas command
I tried adding some more functionality to the batch file below with no success. Is there a way that I can add to this batch file? For example, color 1f and loading some macros would be great too. Thanks. runas.exe /user:Administrator cmd.exe Below is an example batch file (incorporating the command you quoted ) I have working on my system to add some helpful stuff to a new "cmd" window.... *NB Make sure the batch-file is named ADMCMD.BAT and is in the system's "path" (i.e.; in a folder included in the PATH variable, usually C:\WINDOWS is one) and...

array formula Excel 97 (Poker)
Cells A1:A7 each contain a random integer from 1 to 13, with possibl repetitions. I have been trying to create a single formula that will test, for cel A1, for the presence of each of the next lowest four numbers in th range A!:A7 So if A1 contains 9, I would like the formula to return 1 if and onl if 8,7,6,and 5 all occur in the range somewhere, and return otherwise. This is tantamount to testing whether seven cards contain a five car straight headed by the card in A1. I just can't get the right combination of array or sumproduct to d it. I just know ther must be one........... Bil...

Excel and ADD INS
Using Automation, I'm trying to startup an Add-In to Excel called Crystal Ball. Does anyone know if there is an API or how I can go about doing this within Excel programically ? ...

Left Join dilemma is this possible?
I am trying to create a recordset in Excel 2000 from two Access 2000 tables to include all items from the first table "Liquor" and only items from the second table "LiquorOrders" that match either a date or an order number. I have 19 items in the first table and I would like to select all 19 items and only these 19 items with a Status = True, with corresponding data from the "LiquorOrders" with blanks where an order for an item was not placed on that specific date or order number. This at first seemed possible but after much researching and experimentation I hav...

vlookup problems with left
I'm using vlookup,i have a 10 digit number which i have to mach with 9 digit number (last digit is some kind of control digit) and when i use left to take only the first 9 digits =VLOOKUP( LEFT(F200;9);W198:W201;1;FALSE) i get #N/A if i manuallly delete the 10th digit i get mached data. is there a problem with using left on vlookup? =left() returns text. That will never match a real number. I'd take the integer amount after dividing by 10. =vlookup(int(f200/10);w198:w201;1;false) But if you're really only looking to see if there's a match, you could use: ...

How do I make column on the left side of Outlook[inbox,Sent etc.]
I want to add a column on the left side when I am in Outlook that shows (Inbox, Sent Items, Deleted items etc...) How is this done when in Outlook, Windows XP (SP3) Home Edition? "John" <John@discussions.microsoft.com> wrote in message news:6FA61CD3-8CE3-4DFA-B7B7-A27BCF0C29A6@microsoft.com... > I want to add a column on the left side when I am in Outlook that shows > (Inbox, Sent Items, Deleted items etc...) How is this done when in Outlook, > Windows XP (SP3) Home Edition? Enable the Navigation Pane. Press Alt+F1 or click View>Navigation Pane. ...

Did Publisher 2007 get left behind?
Did Publisher 2007 somehow escape the Office 2007 redesign that was done to Word, Excel, etc. I don't see the ribbon and it doesn't appear to be using the .pubx filetype. Publisher is Office's black sheep... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Lurfys Maw" <invalid@invalid.invalid> wrote in message news:haql44p59ts3k3j7b9rd2nkldnliqrk9qd@4ax.com... > Did Publisher 2007 somehow escape the Office 2007 redesign that was > done to Word, Excel, etc. I don't see the ribbon and it doesn'...

Exporting Mail box after employee has left company
I'd like to export a mailbox from Exchange 2003. The employee has left the company, and allthough I haven't deleted his user account yet, I would like to know if it is possible to export his account without actually logging in with his username & password and opening outlook as that user. Thanks, Nate Weldon yes; use exmerge.exe to accomplish this... "Nate Weldon" <NateWeldon@discussions.microsoft.com> wrote in message news:674CD1FE-F33E-4734-BC2C-B20210B95A3C@microsoft.com... > I'd like to export a mailbox from Exchange 2003. The employee has left th...

formula to add a number to a long string **
I'm not quite sure how to best describe this: I need a formula that will take the example below and return the "final result" Col. A = 24.43.234.555 and Col B= 23 making Final Result = 24.43.234.578 Is there a way to do this? This seems to work: =LEFT(A1,LEN(A1)-3)&(RIGHT(TEXT(A1,"0"),3))+23 HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "MMangen" wrote: > I'm not quite sure how to best describe this: > > I need a formula that will take the e...

How do i add " to the beginning of all cells
I have a login script that has lost its quote marks. example: DisableChangePassword=dword:00000001 It should read: ::\"::DisableChangePassword::\"::=dword:00000001 I have already added the quotes to the far side: DisableChangePassword::\"::=dword:00000001 But i am having trouble adding the first quote mark. Oh by the way there are over 500 entries to be made, so one at a time is not what i want to do. Also not all rows need the quote marks. Thank you for your help!! -- yvanblo ------------------------------------------------------------------------ yvanblo's Profile...

Array range error question
When i try to redim my array i get an out of range error. can someone tel me why? Cells A1..A10 are filled with strings. thanks much Sub Get_names_array() Dim names_array() As Variant 'dynmanic Dim j As Long ReDim names_array(5, 1) j = 0 Range("A1").Select For i = 1 To 10 names_array(j, 0) = ActiveCell.Value names_array(j, 1) = ActiveCell.Address arraylimitFirst = UBound(names_array, 1) 'returns current dimension of 1st array limit, init = 5 If j >= arraylimitFirst Then ReDim Preserve names_array(10, 1) ' << RANGE ERROR ...

HELP: Add textbox to a frame at run time
Hi All, How can I add text boxes on the form at run time based on what user inputs in a textbox? For eg: I have a textbox "Total Students" , so Once I input a number in that textbox, I want to have that many textboxes on the form during run time. If I input 4 I want four textboxes to be displayed on the form. Thanks in advance ...

i need to add more options to a formula but i don't know how
hi, on one of my worksheets i use this formula =SUMIF(G126:G242,"UNPAID",F126:F242) this tells excel to add up all our unpaid invoices and tell me how muc we are owed. I now want the formula to add up all the boxes with unpaid next t them, plus all boxes with these phrases next to them: received-not banked yet banked-not cleared yet so i have tried things like thi =SUMIF(G126:G242,"UNPAID(or)received-not banked yet(or)banked-no cleared yet",F126:F242) but it does not work. as you will have guessed i am not too great wit excel. :confused: incidentally, unpaid, re...

Array?
I Have a list like the one below I would like to writre a formula that places the id numbers along side each other in columns the the vehicle, price and insurer is the same. Any help would be appreciated. Vehicles Description Price Insurer ID Holden - Other $228.00 CIC-Alianz 409 Holden - Other $228.00 CIC-Alianz 416 Holden - Other $228.00 CIC-Alianz 829 Holden - Other $228.00 CIC-Alianz 836 Holden - Other $228.00 QBE 202 Holden - Other $228.00 QBE 209 Holden - Other $228.00 QBE 412 Holden - Other $228.00 QBE 419 Public Sub ProcessData() Dim i As Long Dim LastRow As Long With ActiveS...

add contacts to email addressbook, when option greyed out?
if i goto contacts/properties/outlook address book , the option to "show this folder as an address book is greyed out. i cannot use the conternts folder or any othe folder i have created as a adress boook it always remains greyed out. any ideas please? Create a new mail profile - you have given precious little information on your setup but I would bet dollars to donuts that you attempted to transfer your data using Windows Easy Transfer. That never works for OUtlook data without creating a corrupt profile. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep ...

Table Lookup formula where 2 known values are inside array
I am using Excel. Please help, I am stuck on a non-typical table lookup I regularly use =lookup to get values where the relationship is 1:1 e.g. in a cell, I put a value, I use =lookup for that value's meaning I need to do this, but the lookup is a table, not a 1:1 column match Row 1 is a header, one of my search values Column 1 is a value I want to find the table (array) contains the other known value I need do do a enter values in 2 cells, and have a lookup return a value based on the value it looks up. eg: -- | 1 | 2 | 3 | 4 w | a | d | h | i x | b | e | i | ...

Any way to add tables and have the sync out?
Is there any way to add a table to the CRM db and have it sync out to the MSDE databases properly? Am I wishing? If I used a separate database on the MSDE, how difficult is remoting? You can add a table to the CRM db but it will not sync to the remote one. guess your looking at writing a custom solution "Jake Horn" <jhorn@sequelnow.com> wrote in message news:eQDqYM%23pDHA.2216@TK2MSFTNGP12.phx.gbl... > Is there any way to add a table to the CRM db and have it sync out to the > MSDE databases properly? Am I wishing? > > If I used a separate database on the MS...

Is it possible to add Credit Memos to Check Distribution Report?
In GP Check Distribution Report (Reports - Purchasing - Check Information - Check Distribution) is it possible to add Credit Memos that are applied to Invoices on a check? Currently this report by default only shows the Invoices the Check is paying off, but if possible we'd like to show any credit memos associated with invoices on this report. Thanks very much!!! Definitely NOT without a Customization. -- Regards, Vaidy "GPLoveHateRelationship" wrote: > In GP Check Distribution Report (Reports - Purchasing - Check Information - > Check Distribution) is it poss...