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!
.