Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
where exactly do you need to use the operator? –  tumchaaditya Jul 11 '12 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 Jul 11 '12 at 8:51

3 Answers 3

up vote 3 down vote accepted

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

share|improve this answer

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

share|improve this answer
2  
Neat little trick you might want to add to your answer: get-digital-help.com/2011/02/09/… –  soandos Jul 11 '12 at 8:59
    
Thanks for the trick :) –  mmdemirbas Jul 11 '12 at 13:58

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.