Use Excel's Intersect operator to evaluate common cells

Sometimes, there's an easier way to perform calculations on your data. One good example: Excel's Intersect operator replaces complex functions with a simpler formula.

You're probably very familiar with Excel's Range operator. That's the colon character (:) that combines cells between and including two references. In contrast, Excel's Intersect operator (a space character) evaluates cells that are common to two references.

Using the spreadsheet below, you can quickly discern that you'd use the Range operator to evaluate quarters and regions. But what if you wanted to evaluate quarters 1 and 2 for a single region? In other words, how would you evaluate values that are common to quarters 1 and 2 in just the eastern region? A simple SUM() can't do it, so you might consider a complex lookup function, but the Intersect operator would be easier. For instance, using Intersect, you can quickly learn the total of Qtr1 and Qtr2 for the East region using the following formula:

=SUM(Qtr1:Qtr2 East)

Notice that there's a space character between the two reference components. Using the Range operator between the two quarter references combines values in cells B2 through B5 and C2 through C5. Then, the Intersect operator between the quarter references and the regional reference (East) evaluates just the cells that are common to both Qtr1:Qtr2 and East. In this case, that's the sum of 111.59 and 455.05 (cells B4 and C4).

Of course, the expression =B4+C4 returns the same result and is even simpler if you know the exact cell addresses. Using range names is almost always easier than referencing specific cells, especially in a large worksheet. (You can use cell references with the Intersect operator.)


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.


does this only work for excel 2007?


Copied and pasted from Excel 2003: (space) Intersection operator, which produces one reference to cells common to the two references (B7:D7 C6:C8)


Intersection has been around for a long time.