Sum of the intersect of two named ranges

S

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
 
G

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

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"))
 
S

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
 
R

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

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>
 
R

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top