Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SOS! how do I 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, such that the 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? Thanks a lot! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SOS! how do I do multiple-value lookup in Excel 2007?
The only way I can think of is with a macro. If that's OK, then try this:
======== Option Explicit Sub ZeroSum() 'JBeaucaire (11/22/2009) 'Sum of values on sheet2 that match zero value cells addresses on sheet1 Dim RNG As Range, cell As Range For Each cell In ActiveSheet.UsedRange.Cells If cell.Value < vbNullString And cell = 0 Then If RNG Is Nothing Then Set RNG = cell Else Set RNG = Union(RNG, cell) End If End If Next cell MsgBox Application.WorksheetFunction.Sum(Sheets("Sheet2") .Range(RNG.Address)) End Sub ========= Obviously, you can change the MSGBOX to something else that stores the value in a cell somewhere. How/Where to install the macro: 1. Open up your workbook 2. Get into VB Editor (Press Alt+F11) 3. Insert a new module (Insert Module) 4. Copy and Paste in your code (given above) 5. Get out of VBA (Press Alt+Q) 6. Save your sheet The macro is installed and ready to use. Press Alt-F8 and select it from the macro list. Does this help? -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "LunaMoon" wrote: 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, such that the 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? Thanks a lot! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Lookup | Excel Discussion (Misc queries) | |||
Excel 2007 - Multiple If Then or Lookup | Excel Worksheet Functions | |||
Sum Result of lookup in excel 2007 | Excel Worksheet Functions | |||
Excel 2007 Lookup | Excel Worksheet Functions | |||
Excel 2007 LOOKUP Problem? | Excel Worksheet Functions |