Tom’s Tutorials For Excel: Summing The Intersection of Two Ranges

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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
5 comments on “Tom’s Tutorials For Excel: Summing The Intersection of Two Ranges
  1. Joseph says:

    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

    • Tom Urtis says:

      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

  2. 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/

  3. Rajesh Sinha says:

    Nice one Tom,, Data intersection can be handled by the Named Ranges,, ☺

Leave a Reply

Your email address will not be published. Required fields are marked *

*