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