# Union and Intersect

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.

Posted in Uncategorized

## 17 thoughts on “Union and Intersect”

1. jkpieterse says:

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

2. Rob van Gelder says:

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.

3. ross says:

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

4. Ola Sandstrom says:

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

5. jay says:

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.

6. Rob van Gelder says:

Jay,

Thanks Jay – nice spotting

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

A pseudo-union.

Rob

7. Jim says:

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.

8. Benny says:

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

9. Rob van Gelder says:

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.

10. Ed Ferrero says:

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.

11. noname says:

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)

12. lhm says:

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.

Posting code? Use <pre> tags for VBA and <code> tags for inline.