A couple of rarely used features of worksheet formulas are Unions and Intersects.

**Unions**

Most Excel users have probably have come across Union – the combination of ranges to be treated as one range. eg. =SUM(A1, A5:A11, C10)

If you start by typing the formula =SUM( then hold down CTRL and select ranges, Excel automatically defines a Union for you.

The ranges are separated by a comma. The comma is referred to as a Union operator.

I’ve seen Unions used for a Grand Total by summing Sub Totals

**Intersects**

Just like the Union operator is a comma, the Intersect operator is a space.

When you separate ranges with a space then the range used is where they intersect. eg. =A1:C1 B1:B3 will return the same result as =B1

I’ve never seen Intersects used in the wild. I’ve only recently discovered it myself.

It’s interesting to note that in this sample changing Feb to Jan =SUM((Apple, Banana) Jan) does not result in a Circular Reference.

I knew about the intersect and union operators, but I didn’t know you could nest them!

I can’t help but feel there is hidden potential with intersects. It looks very similar to a lookup.

The Operator Precedence orders Intersect before Union which is why I used a bracket.

when i saw this, i started to thing about using Intersect with row and coloum, could be slow though.

Great!

Intersect by a blank separator is new to me. I’ll definitly use it.

Ola

This is also new to me: http://www.nsl.com/k/excel.k

First, nice & useful blog – thanks!

Second, I’m not convinced the union operator is really an operator and not just a piece of microsoft rhetoric. I’ve never seen it used in a context where it couldn’t ALSO be interpreted as a comma separating arguments (e.g., in a SUM function). For example, the following array formula, which uses an intersect operator, works:

{SUM(IF((A1:A10 A1:10)=”a”,1,0))}

whereas this one, which uses the so-called “union operator” returns a #VALUE! error

{SUM(IF((A1:A5,A6:10)=”a”,1,0))}

This is enourmously frustrating because an authentic union operator could be extremely useful when doing, e.g., complex database lookups.

Jay,

Thanks Jay – nice spotting

=A1:A5, A8:A10 is valid, but always returns #VALUE!

A pseudo-union.

Rob

This looks like it has a lot of potential for me. I have created named ranges which represent columns of unique parameters, and named ranges which represent groups of rows to partition the source of the data. What I would like to do is copy the several cells which are the interseection of the parameter range with a particular group of rows. I.e. column AB is named param1 and rows 10 to 20 are named datasetA and are several readings I want to do some computing on. What I have been experimenting with, without success, is using something like:

intersect(range(“param1?),range(“datasetA”)).copy worksheets(“sheet2?).range(“A1?)

Suggestions?

Thanks, Jim.

This is an informative page, with almost maliciously incorrect terminology.

The [space] character is, indeed, an “intersect operator.”

The [comma] character is IN NO WAY a mathematical “union operator.” It is in fact “set addition,” which SQL calls “union all.”

A union returns a SET (in this case, a SET of cells). Set Addition returns a MULTISET. The comma is Set Addition, because using Comma with the same cell twice returns TWO (of the same) cell. For instance:

3=COUNT(C2,C2,C2)=COUNT((C2,C2,C2))

If comma were really a union operator, these counts would return 1!

A less useless example is taking a “plus” shaped range:

6=COUNT(F4:H4,G3:G5)

Obviously, that plus-shaped range has 5 cells. Cell G4 is counted twice, PRECISELY because the comma-character is NOT a union operator.

How sad, that Excel does not support mathematical union (without using VBA).

-b

Benny

You may be correct, but Union is the label as assigned by Microsoft.

ptgIsect: Intersection (ptg=0Fh)

Computes the intersection of the top two operands. This is the Excel space operator.

ptgUnion: Union (ptg=10h)

Computes the union of the top two operands. This is the Excel comma operator.

ptgRange: Range (ptg=11h)

Computes the minimal bounding rectangle of the top two operands. This is the Excel colon operator.

So, if we define a named range NotFirst =Sheet1!$2:$65536,

then we can use a formula in cell A1 like this

=SUM((C:C) NotFirst)

to sum the whole of column C without getting a circular reference.

It’s a shame that there is no inverse of a range operator in Excel.

Something that would let me specify =NOT(A1,A:A) and return A2:A65536.

No clever comments about the number of rows in 2007, thanks.

Ed: Check the SubtractFirstPrinciples function at http://www.dailydoseofexcel.com/archives/2007/08/17/two-new-range-functions-union-and-subtract/

You can use it as in =SUM(SubtractFirstPrinciples(A:A,A1)) though it is quite slow if you give it an entire column in 2007.

hi,

in that case if comma is a union operator, how would you do a UNION of multiple non-adjacent holiday date ranges (rng1,rng2,rng3) and return a single range “Rng”, which then can be feeded to a workday function? (i am not talking VBA but excel worksheet formulae).

Rng = (Rng1,Rng2,Rng3)

=workday(startdate,no,Rng)

noname: Maybe

Specify a dis-contiguous range for an Excel function argument

http://www.tushar-mehta.com/excel/tips/multi_area_argument.html

tushar: multi-area ranges won’t work in WORKDAY.

noname: instead of (Rng1,Rng2,Rng3) you can try CHOOSE({1,2,3},Rng1,Rng2,Rng3) in the third argument. This assumes the ranges are single cells or single column ranges of equal size. If column sizes are unequal use IFERROR(…,0) to ignore any errors.