You should have told us if this is a VBA question or a worksheet formula
If a worksheet formula question, then this worked fine for me...
On the other hand, if this is a VBA question, I think you will need to do it
TheSum = WorksheetFunction.Sum(Intersect(Range("Range1"), Range("Range2")))
Rick Rothstein (MVP - Excel)