Excel - Sum of the intersect of two named ranges

Asked By shikarishambu on 01-Feb-11 10:07 AM
I have an excel worksheet with multiple named ranges - one that is
horizontal and another that is vertical

Range1 = A1:Z5
Range2=  C1:C60

I want to sum the intersect of these two - i.e cells C1:C5

I tried sum(Range1 Range2) and got something else. How can I sum the
intersect of two ranges.

TIA




GS replied to shikarishambu on 01-Feb-11 10:11 AM
shikarishambu was thinking very hard :

Try using the Intersect function.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
GS replied to shikarishambu on 01-Feb-11 10:30 AM
It happens that shikarishambu formulated :

Actually, C1:C5 is not intersecting. it is a contiguous range. To sum it:

myVar = Application.WorksheetFunction.Sum(Range("C1:C5"))

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
shikarishambu replied to GS on 01-Feb-11 11:53 AM
Sorry, I did not understand what you meant by


C1:C5 is the intersection of the two ranges A1:Z5 and C1:C60 - maybe,
not in the excel sense but in the real sense.

I cannot use what you provided because the "intersection" may vary
depending on the named range bounds.

TIA
Ron Rosenfeld replied to shikarishambu on 01-Feb-11 12:56 PM
I cannot reproduce your error.

Are you sure you have defined the ranges appropriately, with absolute references?
Post an example of your data, your expected results, and your actual results.
GS replied to shikarishambu on 01-Feb-11 02:19 PM
shikarishambu brought next idea :

This begs me to ask how you know you need to sum C1:C5. If your code
returns this ref based on whatever criteria/context then why cannot you
use it as I suggested?

These cells are common to both ranges. You'll want to take a look at
VBA's Intersect() method. The question remains how does your project
know which cells it needs to work on? If this is not a ref to the
target cells then I do not understand what you are doing (or trying to
do).<g>

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Rick Rothstein replied to shikarishambu on 01-Feb-11 09:30 PM
You should have told us if this is a VBA question or a worksheet formula
question.

If a worksheet formula question, then this worked fine for me...

=SUM(Range1 Range2)

On the other hand, if this is a VBA question, I think you will need to do it
this way...

TheSum = WorksheetFunction.Sum(Intersect(Range("Range1"), Range("Range2")))

Rick Rothstein (MVP - Excel)