Intersection of Overlapping Ranges:Space Operator

Posted: May 13, 2013 by Transformer in Excel, VBA
Tags: ,

We all know about “: operator. If one writes A1:A10 then it refers to a continuous range from A1 to A10. There is one more operator Space operatorIf this operator is used to separate two or more overlapping ranges then it refers to intersection of those ranges.

e.g.  A3:M3 D1:D6 would return D3 since it lies at the intersection of the two ranges.

Intersection of two Ranges

It can be used with formulas as well. Let’s say from the above data if one wants to see sum of P1 and P2s sales in Mar then it can be calculated using the following formula.

=Sum( A3:M4 D1:D6)

Here two ranges have been seperated by SPACE.

It can also be used in VBA code to refer the intersection of overlapping ranges.
e.g. Range(“range1 range2 range3  ………”)

 

 

Advertisements

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s