8

Does anyone know if there's a union operator in Excel 2010?

I have tried ; and ,, neither of them seems to work. For instance, =E2:E3;E4:E6, =E2:E3,E4:E6, {=E2:E3;E4:E6} and {=E2:E3,E4:E6} always return an error.

Improve this question
2
  • where exactly do you need to use the operator?
    – tumchaaditya
    Commented Jul 11, 2012 at 8:28
  • Can you clarify if you want to apply a function such as SUM, or whether you want to concatenate the cells in the range?
    – datatoo
    Commented Jul 11, 2012 at 8:51

6 Answers 6

Reset to default
14

Excel's 'union' operator has always been the comma* (e.g. A1:A9,F2:G4) while its intersect operator is a single space. It's not that it doesn't work, it's just that not all Excel functions can accept this reference method.

Here are some that do:

  • SUM()
  • COUNT()
  • COUNTA()
  • SMALL()
  • LARGE()
  • MIN()
  • MAX()
  • AVERAGE()

However, when you think about it, the , does not exactly do mathematical [union][1], but rather set addition. For example, =COUNT(A1,A1,A1) returns 3. If it were truly a union operator, this formula would return 1.

*Some Euro-versions use the semicolon (;) instead, depending on the computer's regional settings.

(Further reading: Microsoft Office: Calculation operators and precedence) [1]: http://en.wikipedia.org/wiki/Union_%28set_theory%29

Improve this answer
4
  • 4
    I came across this just now while trying to answer another question - the above is correct but I think, technically, a union needs to be enclosed in parentheses - if you are using SUM function then using =SUM(A1,B1,F1) is just using the functionality of SUM which allows you to add multiple references - you can see a union properly in SMALL function, e.g. SMALL((A1,B1,F1),2) - if you don't use the internal parentheses that doesn't work - You can also use this in RANK and FREQUENCY functions
    – barry houdini
    Commented Mar 23, 2015 at 14:27
  • 1
    Now that SUM, AVERAGE etc. functions allow up to 255 arguments I doubt this is as helpful as it might have been once.....but another use of the union is to allow more discontiguous cells to be used e.g. =SUM(A1,B1,F1) is using 3 references while =SUM((A1,B1,F1)) is only 1
    – barry houdini
    Commented Mar 23, 2015 at 14:30
  • 1
    Note that there is nothing function-specific about this operator, it works everywhere. For example =(A2,C8,C10) A2 will give the intersection of A2 and (A1,C8,C10) just to give an insane example. Note that in general you'll want to avoid this
    – dtech
    Commented Jun 23, 2015 at 11:51
  • There are functions where it is not true like LINEST and this answer doesn't work.
    – Maja Piechotka
    Commented Oct 25, 2017 at 21:20
6

If you want to sum values, you can write this:

=SUM(I3:M3;I4:M4;I5:M5)

If you want to merge texts, you can write this (or use your mouse as @soandos says):

=CONCATENATE(A1;B1;C1;D1)

It looks like you want to write =CONCATENATE(A1:D1). But, as I know, it is not possible (gives error).

Improve this answer
1
4

In short the answer is NO.

However, for some functions you can use the following workaround,

=COUNTA(B1:B3,A2:C2)-COUNTA(B1:B3 A2:C2)

Note that the ranges have one cell in common (ie intersecting). The "," is used by some functions to allow multiple range to be provided as parameter. This is not a union as this will include the intersection cell twice. It will be COUNTED TWICE. So, by subtracting the intersection you get the correct answer.

This technique should work with the following:

SUM() COUNT() COUNTA()

It won't work with: SMALL() LARGE()

The formula can be modified to work with:

AVERAGE()

It is never required with MAX and MIN!

It's a real pain that it works like this and that there is no UNION operator.

In VBA you can write a function that create a proper union:

see here for info http://www.cpearson.com/excel/BetterUnion.aspx

ie 

 Function ProperUnion(ParamArray Ranges() As Variant) As Range
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ProperUnion
' This provides Union functionality without duplicating
' cells when ranges overlap. Requires the Union2 function.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim ResR As Range
    Dim N As Long
    Dim R As Range

    If Not Ranges(LBound(Ranges)) Is Nothing Then
        Set ResR = Ranges(LBound(Ranges))
    End If
    For N = LBound(Ranges) + 1 To UBound(Ranges)
        If Not Ranges(N) Is Nothing Then
            For Each R In Ranges(N).Cells
                If Application.Intersect(ResR, R) Is Nothing Then
                    Set ResR = Union2(ResR, R)
                End If
            Next R
        End If
    Next N
    Set ProperUnion = ResR
End Function
Improve this answer
3

The question is not too clear, but for now, it seems like you have to merge texts. So for instance – as per your example if you have to union E2, E3, E4, E5, E6 you can write a formula like: 

=CONCATENATE(E2,E3,E4,E5,E6)

I tried it myself: You have to individually describe cells. ":" This is not working for me too in Concatenate. So you should try as explained above.

Else, mmdemirbas's answer perfectly explains the SUM formula.

Improve this answer
1

Disjoint ranges can be specified using extra colons, instead of a comma :

For instance, with LINEST, when your known X's come from disjoint ranges:

LINEST(B1:B20, A1:A20:C1:C20, True)

Note the colon between A20 and C1.

This works in any function, though seems to be mostly undocumented.

(The top answer here may have been correct once. But not in recent Excel versions)

Improve this answer
0

For Office 365, the following formula can union numbers in column A and B.

=UNIQUE(SMALL((A:A,B:B),SEQUENCE(COUNT(A:B))))

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .