Tom’s Tutorials For Excel: Summing The Intersection of Two Ranges
Here’s a way to structure a SUM function for operating on an intersection of rows and columns.
In the picture, there are row and column totals for the entire year’s Income budget. But suppose you only want to look at, say, Quarter 3, and only for, say, certain income streams such as Training, Disk Sales, and Videos. And further suppose you prefer to think of your services in terms of their occupied rows and columns.
The formula in cell N1 is =SUM(H:J 9:11).
Notice the construction:
One piece of the intersection is columns H, I, and J.
Another piece is rows 9, 10, and 11.
Those two components are entered as you see, separated by a space.
Hi Tom,
What a nice trick! I never knew you could do that. I bet there are some other useful tricks with finding an intersection like that 🙂
Nice work!
Joseph
Thank you Joseph! Excel does have some cool tricks and formula construction opportunities to extract data. I have more examples getting ready to be posted, so visit often!
FYI, you can also find me on Twitter, posting half a dozen Excel tips on most business days, at https://twitter.com/#!/TomUrtis
Hi Tom,
This is one of my favorite Excel tips!
It works great with two named ranges to see what cells are in both ranges.
Cheers,
Kevin Lehrbass
http://www.myspreadsheetlab.com/
Thank you!
Nice one Tom,, Data intersection can be handled by the Named Ranges,, ☺