This article shows how to calculate sum with intersecting tables using SUM function and intersect operator, the space character (" ").
Syntax
=SUM(first range second range)
Steps
- Type =SUM( function
- Select or type the first range reference that includes data C5:H6
- Press Space key to add a space character
- Select or type the second range reference that includes data D3:E8
- Type ) to close function and finish the formula
How
The space character (" ") is the intersect operator in Excel. Putting a space between two ranges returns the intersection cell or range that two ranges shares. You can see how it works even by itself, if both intersecting ranges are single dimensional as well. For example;
=C5:H5 D3:D8 formula points D5 cell and returns the value of D5, 99.
If the intersection is a range of cells and places outside a function, it will return #VALUE! error. However, it will operate as a range if you it inside a function that accepts ranges.
=SUM(C5:H6 D3:E8) returns 309 that is sum of numbers in D5:E6 range.
If you try to intersect two not-intersecting ranges, e.g. C4:H4 C6:H6, the formula will return a #NULL! error.
Also see related articles how to calculate average with intersecting tables.
