Count values with Excel intersect operator

Excel intersect operation is handled by the space character (" "). You can intersect multiple rages by space in range supporting formulas. This article shows how to COUNT intersecting ranges with COUNT function and Excel intersect operator.

Syntax

=COUNT(first range second range)

Steps

Type =COUNT( 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 actually intersect operator for Excel. Putting a space between two ranges returns the intersection cell or range that two ranges shares. For example;

=C5:H5 D3:D8 formula points D5 cell and returns the value of D5, 99.

If intersection is a range of cells, it will return #VALUE! error like other range returning formulas as well. However; using them in a function that accepts ranges is perfectly viable.

=SUM(C5:H6 D3:E8) returns 309 that is count of numbers in D5:E6 range.

If you try to intersect two non-intersecting ranges, e.g. C4:H4 C6:H6, the formula returns 0.