Background
Sometimes we have to sum the intersections of multiple ranges.
Can you think of a formula? Using INDEX MATCH? INDIRECT? And nested IF function?
I go through exactly the same thought process until I meet Intersection operator.
Don’t panic if you don’t know about intersection operator. Only very few Excel users know about this.
In this tutorial, I will show you how to sum intersections of multiple ranges using mysterious intersection operator. I promise that the formula will be super short.
Sample Workbook
In this article, I will use the following table as an example.
What we would like to achieve
To sum the intersections of Column A and Row 3-4
Download the workbook to practice it by yourself!
Press the download button!
General Formula
Official Syntax
=SUM(Range1 Range2)
Dollar Excel Syntax
=SUM(Put the first range that forms the intersection here Put the second range that forms the intersection here)
Does this surprise you? Instead of putting a comma like we usually do, we put a space here.
The space here is known as an intersection operator.
It means “Please operate whatever the function does only on the intersections of this two ranges”
Formula of Cell F8
=SUM(B1:B8 A4:D5)
Since we want to find out the sum of the intersections between column B and row 3-4, we put B1:B8 and A3:D4 in the formula and put a space between this two ranges.
Formula with Named Range
To better illustrate this I gave the each column and row a header.
To start with, we will need to name those ranges first.
Read How to Name Multiple Single Cells in Excel? if you forget how to name multiple ranges in a few clicks 🙂
Formula of Cell F8
=SUM(Eve Spanish:Korean)
Between “Eve” and “Spanish”, we need to put a space as the intersection operator.
Between “Spanish” and “Korean”, we need to put a colon to tell Excel that we want to combine that two range as one.
So, this tells Excel “Please sum the intersections between “Eve” range and “Spanish” to “Korean” range.
We will still get the 16 as a result.
Do you find this article helpful? Subscribe to our newsletter to get exclusive Excel tips!
