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.

Union and Intersect Example

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

14 thoughts on “Union and Intersect

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

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

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

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

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

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

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *