This Excel Factor tip was sent in by Kathy Carmel of Santa Barbara, California.
Words by Mynda Treacy.
Kathy uses Excel’s intersect operator to quickly SUM a cell, or range of cells at the intersection of two ranges.
You’re forgiven if you’re thinking I’ve just swallowed a load of Excel syntax, so let me show you an example.
I call this technique the Lazy Lookup, and there’s nothing wrong with being lazy, or perhaps you might like to call it efficient, as it sounds, well, less lazy 🙂
Lookup a Single Cell
We’ll take this table of data:
And let’s say I wanted to find the value for T-Shirts for FY2011; I could simply enter this formula:
Obviously I could also enter =D3 but then I'd need to know that that was the cell reference, which is not usually the case when you're doing a lookup, so we’ll ignore that for now.
Notice how there are no brackets in the formula (you don’t need them to lookup a single cell), and there is a space between the two ranges?
This space is the ‘intersect operator’, and it instructs Excel to find the value in the cell at the intersection of the two ranges, which is D3.
You could also use this formula to return text, since there is only one cell at the intersection.
Intersection of Named Ranges
While the above example works, it’s a bit laborious to enter the cell ranges, and since this is a lazy lookup we need to make it quicker and easier to enter.
To do this I’ve set up the following named ranges for each column and row in my table:
And so on…
Tip: I set up all these named ranges in just a few steps:
- Highlight the table, including headers
- CTRL+SHIFT+F3 to create named ranges from selection. This opens the following dialog box:
- Click OK
Now I have my named ranges set up I can write the formula above like this:
Again, no brackets, just a space between the two names. I think you’ll agree this now meets the ‘lazy’ requirement quite nicely.
SUM Multiple Cells
What say you wanted to SUM FY2010 and FY2011 for Skirts and Shorts?
You could use this formula:
- Because there are multiple cells at the intersection you must wrap your named ranges in a SUM function, or AVERAGE, MIN, MAX etc.
- Also, you cannot return text where there are multiple cells at the intersection.
If you want to return the SUM of non-contiguous ranges simply group each set of rows and each set of columns together inside brackets like this:
Make it Interactive
Note: the figures used in this example are fictional (I created them using the RANDBETWEEN function); I would never accept a reduction in my clothing budget year on year. 😉
Thanks to Kathy for sharing the Intersect Operator with us.
Kathy Carmel lives in Santa Barbara, California.
"I work with various forms of health care data, program in SAS and use Excel for deliverables and transitioning data to non-programmers. I have been using Excel for many years, mostly at a relatively basic level, but am now writing macros to do more of the analysis in Excel."
Vote for Kathy
If you’d like to vote for Kathy's tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment to thank Kathy for taking the time to share this tip….or all of the above 🙂