# 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?
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)```