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:
=D2:D8 B3:E3
=2111
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:
FY_2009 =B2:B8
FY_2010 =C2:C8
FY_2011 =D2:D8
FY_2012 =E2:E8
Dresses =B2:E2
T_Shirts =B3:E3
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:
=T_Shirts FY_2011
=2111
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:
=SUM(FY_2010:FY_2011 Skirts:Shorts)
Notes:
- 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.
Non-contiguous Ranges
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
Now for the ultimate in laziness, get someone else to build the formula by giving them data validation lists and use the INDIRECT function to build the formula on the fly.
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 🙂
Dave Kerr says
This tip is brilliant. I had never come across the use of the space to select the intersection of cells.
I had one issue when preparing the named ranges: Excel tagged an underscore to the end of the Year values so the Indirect wouldn’t work. I changed the values from “FY2009” (which became “FY2009_”) to “FY_2009” and then derived the range names. Perfect!
Mynda Treacy says
Glad you liked it, Dave 🙂
Marian Stasney says
Quick question: when you used CTRL+SHIFT+F3 to create named ranges from selection, I assume you were working on a standard keyboard. I use a laptop. Would I also need to use the FN key to select the F3 function?
Great tip!!! Keep them coming!
Thank you
mks
Mynda Treacy says
Hi Marian,
Yes, you would have to press FN as well if you don’t have dedicated function keys on your keyboard.
Mynda
Chandru Parthiban says
Kathy, Very Nice & Useful Tips,
With Thanks and Regards,
Chandru Parthiban
Gordon says
Hi Mynda,
I use intersection of named ranges across multiple workbooks.
Each workbook being identical in structure.
I am hoping to find most efficient syntax for formula like:
Book1.xlsx! Qtr1 Sales + Book2.xlsx! Qtr1 Sales + Book3.xlsx! Qtr1 Sales
But seems like I have to type:
Book1.xlsx!Qtr1 Book1.xlsx!Sales + Book2…….
Is there a more efficient syntax than above ?
Many Thanks,
Gordon.
Mynda Treacy says
Hi Gordon,
I’m not sure what the intersection of named ranges has to do with your formula as it looks like you want to sum/add ranges. If so, you can use a formula like this:
=SUM([Book1.xls]’Qtr 1 Sales’!$B$2:$B$20,[Book2.xls]’Qtr 1 Sales’!$B$2:$B$20,[Book3.xls]’Qtr 1 Sales’!$B$2:$B$20)
Note: Substitute $B$2:$B$20 for your named range if desired.
I hope that helps. I’m not aware of any simpler formulas.
Kind regards,
Mynda.
khalid says
excellent !!
Mynda Treacy says
🙂 thanks, Khalid.
Khurram says
understood and thanks for nice tip.
Mynda Treacy says
You’re welcome, Khurram 🙂
Subash says
Dear Mynda,
I tried the above with exact details as shown above and made the data validation lists and put in the formula =SUM(INDIRECT(B15) INDIRECT(B16)) result I am getting is null error.
You have two hidden rows (10,11) in your example above. Is there anything there?
Kindly advise
Regards. Subash
P.S. All the comments below were posted on my birthday last year. -:)
Carlo Estopia says
Hi Subash,
What post are you referring to?
What example and what file?
Cheers,
CarloE
Subash says
Hi Carlo,
“Make it Interactive” in
“Excel Factor 15 The Lazy Lookup”
Rgds,
Subash
Carlo Estopia says
Hi Subash,
Believe me nothing is hidden in there. lol.
Now, I also tried to walk-through this and we got the same error.
By luck however I followed the named range to the letter: FY_2009, FY_2010, FY_2011, FY_2012.
I surmise you also skipped the underscore(“_”)because we are both ‘lazy’? lol.
Cheers,
CarloE
PS: If this don’t work, don’t ask me again. Ask Mynda. 😛
Subash says
Hi Carlo,
It did work, but in my file the automated name range was FY2009_ and not FY_2009. Lazy eh !
Thanks very much.
Regards.
Subash
Carlo Estopia says
Hi Subash,
I’m the only one who’s lazy. 😉
Anyway, but you still have the underscore.
Cheers,
CarloE
Subash says
Carlo,
Hi, we all are lazy that is where Excel comes in……
Keep up the good work of sharing knowledge my brother.
Regards,
Subash
Phil says
amazing tip, wasn’t aware of the Intersect operator. And again: another lovely use (or lazily use?) of named ranges… Thanks for this tip…
Mynda Treacy says
🙂 Cheers, Phil.
RAVI XAVIER says
THANK YOU VERY MUCH
Mynda Treacy says
You’re welcome, Ravi 🙂
r says
i’m agree with you … “there’s nothing wrong with being lazy” … on the contrary 🙂
nice article, for me particularly interesting the section “Non-contiguous Ranges”
regards
r
Mynda Treacy says
Thank you, r!