SOS! how do I do multiple-value lookup in Excel 2007?

[LunaMoon]

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!

 

[JBeaucaire]

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?