SOS! how do I write a customized function to do multiple-value lookup in Excel 2007?


I have two regions in exact same places of sheet 1 and sheet 2,
I want to find all cells in the region in sheet 1, with cell
content equal to 0,
and then add the numbers at the corresponding locations in the region
in sheet 2 up.
For example, let's say in the region in sheet 1,
there are multiple locations where the cell content = 0:
A1,
B3,
C4,
D2,
etc...
I would like to obtain sum(sheet2!A1, sheet2!B3, sheet2!C4, sheet2!
D2), etc.
How do I do that?
I want to write a user customized function for that: The regions in
sheet1 and sheet2 are fixed to be D7 to Z20.
So the first step is to find all cells in sheet1 in D7 to Z20 with
values=0, and then add up all such cells in sheet2...
How to do that? I am a green hand in using Excel/VBA...
Thanks a lot!
0
12/1/2009 2:36:11 AM
excel 39879 articles. 2 followers. Follow

1 Replies
527 Views

Similar Articles

[PageSpeed] 25

You can do this with built-in functions quite easily.

Does the range to sum, Sheet2D7:Z20, contain only numeric data (empty cells 
would be OK)?

Will any of the cells in the criteria range, Sheet1D7:Z20 be empty? Empty 
cells evalaute as 0.

The general formula to this would be:

=SUMPRODUCT((Sheet1!D7:Z20=0)*Sheet2!D7:Z20)

-- 
Biff
Microsoft Excel MVP


"LunaMoon" <lunamoonmoon@gmail.com> wrote in message 
news:4f89b2a3-30b3-4a8a-806b-6ee3ec540419@e4g2000prn.googlegroups.com...
>
>
> I have two regions in exact same places of sheet 1 and sheet 2,
> I want to find all cells in the region in sheet 1, with cell
> content equal to 0,
> and then add the numbers at the corresponding locations in the region
> in sheet 2 up.
> For example, let's say in the region in sheet 1,
> there are multiple locations where the cell content = 0:
> A1,
> B3,
> C4,
> D2,
> etc...
> I would like to obtain sum(sheet2!A1, sheet2!B3, sheet2!C4, sheet2!
> D2), etc.
> How do I do that?
> I want to write a user customized function for that: The regions in
> sheet1 and sheet2 are fixed to be D7 to Z20.
> So the first step is to find all cells in sheet1 in D7 to Z20 with
> values=0, and then add up all such cells in sheet2...
> How to do that? I am a green hand in using Excel/VBA...
> Thanks a lot! 


0
T
12/1/2009 3:28:55 AM
Reply:

Similar Artilces:

Writing a Macro
I am creating a form in Microsoft Word 2003. There is an area that requests a physical company information, including; Address: City, State, Zip: Phone: Fax: Primary Contact: Email: I would like to make it so that when a box labeled 'same as physical address' is checked that the information that has been entered will autofill to corresponding fields labeled Billing Information, with the following fields: Address: City, State, Zip: Phone: Fax: Primary Contact: Email: Can anyone help me with this? See the Repeating Data page of Greg Maxey's website ...

Max Customers Accounts in CRM 1.2
One of our client who is evaluating CRM asked for number of Customer Accounts Limit of MS CRM 1.2. We know that MS is targeting CRM for small and Medium size orgainzation but can it support customers of an orgainzation to Millions. Or Can some one tell the max limit of Customer Accounts. ? Regards, JZ There is no defined maximum.. The key is sizing the SQL server appropriately. Especialyl with CRM not supporting clustered SQL servers. I know there are companies out there that have implemented (or are in the process of implementing) installs for > 1,000 users. Matt Parks MVP - Micr...

read/write on two accounts
I have in exchange my mailbox and access - read/write - to another account which we use for people to send general question to NJPIES and anyone can read/reply to it. Question: What rights do I deal with so that people can read/reply BUT not delete the message? When I reply, the reply - Sent- always goes out from my account. I will like to reply and the SENT - be sent from that account so that any other user can look at the history. sort of like news groups. Any ideas? thanks, Raul Rego NJPIES Hello: Which version of Exchange and Outlook? I'll use 2003 for...

error value in axis
I am trying to plot a chart for varying number of weeks. I use the formula =IF(J63=0,NA(),J63-1) to stop with zero th week starting from the week I define. I also use similar formulas for getting data i.e =IF(ISERROR(VLOOKUP($K64,$A:$D,2)),NA(),VLOOKUP($K64,$A:$D,2)), where k64 indicates week number. The problem now is the data line stops with 'NA' values as expected, but the axis which contains week number shows 'NA' after the week numbers. Any ways to fix this. Now you need dynamic names (aka named ranges) that know where the numerical data ends, and use these names a...

Excel
Thanks in advance for any assistance. On some machines, when a user opens an Excel file from SharePoint and checks out for edit and -does not- use the local drafts folder, you see a dialog with "File Conversion In Progress", then "Converter Failed To Save File" message, but the file is checked out to the user and will not open in the Excel. A kind-of workaround, is to use local drafts for the Check Out, but if the user tries to launch the file via the link in SharePoint, the "Converter Failed To Save File" message is displayed. Both machines have SP1 and the la...

Custom E-Mail Stationary
Question about a Custom E-Mail Stationary. I recently bought a custom stationary for Outlook 2007. It has two pictures and some text. When setting up the stationary it does not show the pictures and only shows a red "X". It seems that the path of the picture is incorrect. I cannot contact the company who created the custom email stationary. ...

need help replying to multiple email messages
I sell things on the internet, and when I receive say 5 emails for prospective buyers, I would like to be able to highlight all five of those and repsond to all of them at the same time instead of having to cut and paste five addresses individually. this has to be possible - anybody know how to do it? ...

Filters *SOS* Filters *SOS* Filters *SOS*
1) On formLoad, I need to show only those items which are unassigned. 2) I need for the end-under to select only the items they are assigned to. 3) I need an option to select "ALL" so I can view all items. Any insight offered would be appreciated!!! On Wed, 29 Aug 2007 16:24:01 -0700, valdaiway <valdaiway@discussions.microsoft.com> wrote: >1) On formLoad, I need to show only those items which are unassigned. Use a query to put a criterion on the field that defines the assigned status. I assume you know how that information is stored in your table; nobody here does, ...

How do I customize the context menu?
I'd like to customize the context menu in excel (ie - to add and remove commands from the "right-click" menu), but I've been unable to find out how to do this. It does not seem possible to add or remove commands from the context menu via the "Tools -> Customize" menu as the context menu is not visible there. Any information anybody has on how to do this would be must appreciated. Thanks! JJ, The commandbar is actually called "Cell". Here are two subs that show how to modify it. The first will add a smiley-faced button that calls the subroutine nam...

Last Calculated Value
I have a spreadsheet written in Excel 2006 for Windows. It has a function written in Visual Basic. When I open it using Excel Mac 08, it opens in an empty sheet, and I need to arrow to the sheet that's used, where a column has #NAME? for the cells using this function. When I open it with Numbers 08, I get "Last calculated value used" as a warning. The numbers from the calculation I had at work are in those cells. I infer that those numbers are saved, but that Excel Mac won't show me them. What good is having Excel for Mac if it's not compatible with...

customer data import failed to import the address info
I use the intergration manager trying to import a bunch of new customers. The source csv file has the customer id, customer name, address, phone, email, address id, ship to address id, bill to address id etc. After the importing, I found the address ID is created but the address is blank. So I went to table import to import the address but it says the address ID is already existed. Then I ran the table import again and name the address ID differently and it seems working and now how can I update the statement id, ship to id and bil to id address field with table import? I also wanted ...

Form Assistant Customizations
Hello, It appears that the form assistant is not customizable through the CRM Interface, is that correct? If so, does anyone know of which files it refers to so that they could be modified manually (I just need to be able to add javascript to pre-populate some of its fields, no server side code changes) Any ideas are appreciated. Actually if anyone has any knowledge about how that page actually works, like the data source it uses to load its drop list values and fields that would also be handy information. Cheerio. Hi, There is no support for modifying the form assistant. Any changes w...

How to write a table of contents in word
Hi I have problems to accommodate my table of contents in word. 1 Intorduction.......................................................................................5 1.1 Intorduction into topic..................................................................5 1.2 Motivation und objective target...................................................6 1.3 Structure of the work.................................................................... 7 The first item is given in my document. I want to create further sub-items. The problem is that the page numbers 5 in 1st and 2nd line ...

format cell with maximum value
I have a list of number, say 10rows by 10 columns. I want to specify a conditional setting for each cell so that if the cell has the highest value in its row, the cell will be coloured red. So I will get 10 cells that will be coloured red. How do I do that? Thanks Use conditional formatting. If your data starts in A7, then in A7, Format > Conditional Formatting. Use the drop-downs and text box to create your condition: If cell value is equal to =MAX($A7:$J7), then set your format appropriately. Copy the format through your entire table. "Frank Drost" wrote: > I have ...

How do you interactively write to a CEditView Window
(Newbie): I am trying to output status and diagnostic information to a CEditView Window and allow users input annotations to the same window. The User input is handled by Windows. How do I output to the window? How do I ensure that program output is appended to existing text? DrawText erases the screen and outputs to (0,0). What is the magic? (Please) skidmarks On Thu, 17 Apr 2008 10:34:00 -0700, skidmarks <skidmarks@discussions.microsoft.com> wrote: >(Newbie): I am trying to output status and diagnostic information to a >CEditView Window and allow users input annotations...

Insert Functions
Hello, I need to insert the current time and date into excel when a barcode is scanned from an ID card. It requires the [ "ctrl" + "shift" + ";" ] for which I can't find an ansii code for the latter or how to input the combination.. Any ideas how I can put this into the code so it will output to excel when the card is scanned. Also, it would be nice at the end of the code to get the focus to go to the next row, first column after a scan, to be ready for the next card. Any help is greatly appreciated. David A. Powell Hi in VBA why not use something...

XML atribute value
Asking for XML atribute value HI, I m lost when I wanna ask for attributes value’s XML file: hi, my xml file is: <?xml version="1.0" encoding="ISO-8859-1"?> <Message xmlns="http://www.kith.no/xmlstds/henvisning/2005-07-08" xmlns:xsd="http://www.w3.org/2001/XMLSchema.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.kith.no/xmlstds/henvisning/2005-07-08 Henvisning-v1.0.xsd"> <Type V="H"/> <MsgVersion>v1.0 2005-07-08</MsgVersion> <MIGversion>...

Print Customer Statement (or similar report) w/o closing billing c
Print Customer Statement (or similar report) w/o closing billing cycle Is there a way to print a customer statement or similar report before closing billing cycle? None of the reports available would show/print a statement. Anyideas or suggestions? Thanks ...

How can I stop Excel from autoformatting as DATE?
I have a cel in which I am trying to enter a PO# of 10- 8348. Even though the cel is not formatted to adhere to a Date formatting (it is set to General), whenever I exit the cel, the data converts to 'Oct-48' I don't want it to do this! I can't find where in the Autoformat potions I can turn this off. Any help? -mike Mike, Short of turning the machine off, the only thing that comes to mind is to format the cell (before you enter the stuff) as Text. Format - Cells - Number - Text. Or precede each with an apostrophe. ' Earl Kiosterud mvpearl omitthisword at verizon p...

Printer Driver Function
This is a multi-part message in MIME format. ------=_NextPart_000_0052_01C3EAAB.46F4EE70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello I want to create a "print driver function" for print-to-email For eg.=20 I create a document in Excel, or Lotus 123 or any other program which = has the ability to print. I preview it to make sure it is OK. I select = as a printer: "print to email".=20 =20 Then properties button pops up and when I press it I have a choice of = file formats, dpi and compression. The idea is to p...

alter function
Hi, using SQL2005. I want to replace a function and can't because the function is being referenced by an object. I have tried to drop it first and then create the function. This has the same error result. Is there a way to ignore any referenced object and drop a function (and then re-create it). Alternatively any way to ignore referenced object and alter a function. Any ideas or recommendations appreciated :-) Many thanks, Jonathan Hi Havv you created a UDF with SCHEMA BINDING option? "Jonathan" <Jonathan@discussions.microsoft.com> wrote in messag...

Excel Versions #2
I can't seem to open excel 2000 files with Office 95 version. Anyway around this problem. Thanks, Mike C. From the menu, File > Save As, then in the "Save as type" drop down (at the bottom of the dialog) choose "Microsoft Excel 5.0/95 Workbook" -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "Mike Campbell" <anonymous@discussions.microsoft.com> wrote in message news:096701c3c644$01795330$a601280a@phx.gbl... > I can't seem to open excel 200...

vlookup for words content when there are duplicate values
Hi, Can somebody help me here. Thanks in advance. When I vlookup Material 1 to Material 2 for the Project No, I get the vlookup Project No. as 222-56 for all the duplicate Material values. How formula to use so that when there are duplicate values in Material2, I want to have vlookup searching to match Project No. containg "AA" as 1st choice ? (if no Project No. containing "AA", then looks for its content "AB" and subsequently looks for "AC" if there is no "AB"). ColumnA ColumnB ColumnC Material 2 Duplicate? Project No. 224410 duplica...

Customize Outlook Today...
After updating 3 users to Windows XP Service Pack 2, their "Customise Outlook Today" button no longer works. I have ran through the KB article 820575 and it did not make any difference Any ideas You can edit the registry to make changes to Outlook Today. http://www.outlook-tips.net/howto/oltoday.htm -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutio...

Help SOS
I just upgraded my BDC Nt4 server installed with exchange 5.5 to win2k and during the upgrade I selected the role for this server to become a member server in the new Active directory. After upgrading the os, exchange services all fail to run. the user which use to log on all these exchange services is the domain administrator account. I tried using system account to log on but failed. Please help, thankyou SOS Do you have an Active Directory domain? "daniel" <c_weng_fai@hotmail.com> wrote in message news:022d01c3ab84$150b7110$a001280a@phx.gbl... > I just upgraded...