Sum of the intersect of two named ranges

[shikarishambu]

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]

shikarishambu was thinking very hard :

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

Try using the Intersect function.

 

[GS]

It happens that shikarishambu formulated :

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

Actually, C1:C5 isn't intersecting. It's a contiguous range. To sum it:

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

 

[shikarishambu]

It happens that shikarishambu formulated :






Actually, C1:C5 isn't intersecting. It's a contiguous range. To sum it:

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

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Sorry, I did not understand what you meant by

Actually, C1:C5 isn't intersecting. It's a contiguous range. To sum it:

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 can't use what you provided because the "intersection" may vary
depending on the named range bounds.

TIA

 

[Ron Rosenfeld]

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

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]

shikarishambu brought next idea :

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 can't use what you provided because the "intersection" may vary
depending on the named range bounds.

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 can't 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 don't understand what you're doing (or trying to
do).<g>

 

[Rick Rothstein]

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.

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)