Today, I am going to talk about a nifty but not so well known feature of Excel. This is about Intersection Operator in Excel. Before, I go deep in Intersection Operator, I would like to talk about Reference Operators in Excel. There are 3 Reference Operators –
1. Range Operator (represented by Colon) – It specifies a range. Hence, A1:B10 where a colon (:) has been used, specifies all cells contained between A1 to B10. Similarly, D:D specifies entire column D. 3:3 specifies entire row 3.
2. Union Operator (represented by Comma) – It specifies the union of ranges. Union means inclusive of all cells in the ranges specified. Hence, if you specify A1:A5,D2:D4 it will include all cells lying between A1 to A5 and D2 to D4. Hence, if I specify =SUM(A1:A5,D2:D4)
The answer would be 221.
If I specify =SUM(A1:A5,B2,D2:D4,C4,C1:C2), it will pick up all cells from A1 to A5, B2, D2 to D4, C4 and C1 to C2.
The answer would be 326.
The Excel file related to this article can be downloaded from Intersection Operator.
3. Intersection Operator (represented by Space) – It specifies the intersection of ranges. Hence, if I specify =B1:B5 A3:D3
The answer would be 50 as B3 is lying on the intersection of B1:B5 and A3:D3
Note – You will get #NULL Error if intersection doesn't happen i.e. no overlapping cell is found. Hence, if I specify =B1:B3 A4:D4
You can notice there is no overlapping cell below, hence you will get #NULL error.
Order of Precedence of Range Operators in calculation – Colon (Range) has 1st, Space (Intersection) has 2nd order of preference and Comma (Union) has 3rd order in a calculation.
Now, let's go back to Intersection Operator.
1. As I already outlined that if I specify =B1:B5 A3:D3 then I would be getting the value of cell (B3) lying at the intersection of B1:B5 and A3:D3 and answer would be 50.
2. Also, if no cell lies at the intersection of ranges specified, the answer would be #NULL.
3. If I specify =B:B 3:3 which means intersection of column B and row 3 which is B3, then this is equivalent to =B1:B5 A3:D3 which means answer of 50.
4. Now specify =SUM(B:B 3:3,B:B 4:4) then I get intersection of column B and row 3 which is cell B3 + Intersection of column B and row 4 which is cell B4. As I had mentioned comma is for union. B:B 3:3 is B3 and B:B 4:4 is B4. Hence, this becomes equivalent to SUM(B3,B4) and sum gives us 94.
5. Now specify =SUM(B:B 3:3,4:4) then I get intersection of column B and row 3 which is B3 + row 4. Hence, this becomes equivalent to SUM(B3,4:4). Hence answer would be 50 + sum(4:4) = 220
6. Now specify =SUM(B:B,C:C 4:4) then I get intersection of C:C and 4:4 which becomes C4 which gets unioned with B:B. Hence this is equivalent to =SUM(B:B,C4). Hence, answer would be 253.
7. Now specify =SUM(B:B 2:4) which is intersection of column B with rows 2, 3 and 4. This is equivalent to =SUM(B:B 2:2,B:B 3:3,B:B 4:4). Hence answer is SUM(B2,B3,B4) = 99
8. Now specify =SUM(B:D 3:3) which is intersection of columns B, C and D with row 3. This is equivalent to =SUM(B:B 3:3,C:C 3:3,D:D 3:3). Hence answer would be SUM(B3,C3,D3) = 128
9. Now specify =SUM(B:C 2:5) which is intersection of column B with rows 2 to 5 and column C with rows 2 to 5. This is range B2:C5. Hence, answer would be 246.
9. Now, let's specify =SUM(B:B,2:2 D:D,4:4) then this is equivalent to SUM(B:B,D2,4:4), hence adds up to 434
10. Let's specify =SUM(B:B 2:2,D:D 4:4) which becomes equivalent to SUM(B2,D4), hence answer would 27.
10. Last case to specify is =SUM(B:B 2:2,D:D 2:2,B:B 4:4,D:D 4:4) which is equivalent to SUM(B2,D2,B4,4). Hence, answer would be 106.
