Count range overlap cells

[N1GHTEYES]

Hi. In a workbook, I am trying to write a function in a cell which returns the size of the overlap between two specified ranges. I think the range intersection operator is a space, so I want to write something like: = Count($A$1:$E$5 B2:F6), and I expect to get the answer 16 (i.e. the number of cells common to both ranges.
This does not work.
Can anybody suggest what I need to do please?

Thanks.

 

[Deniall]

I am not aware of how you might do this entirely in the Excel environment, but in the VBA environment you can use the Intersect method.

Intersect(Range("a1:d4"),Range("c3:g7")).Address
returns
$C$3:$D$4

Intersect(Range("a1:d4"),Range("e5:g7")).Address
has a null return, meaning that the expression
Intersect(Range("a1:d4"),Range("e5:g7")) Is Nothing
is True.

[sub][ ]—————————————————————————————————[/sub]
[sup]Engineering mathematician/analyst.[ ] See my profile for more details.[/sup]

 

[mintjulep]

= COUNTBLANK( $A$1:$E$5 B2:F6)+COUNTA( $A$1:$E$5 B2:F6)

Works for me.

COUNTBLANK() counts the blank cells and COUNTA() counts the not empty cells

In older versions of Excel you might need to force that as an array formula by using cntrl+shift+enter

Newer version automatically make array formulas where an array is returned.

 

[N1GHTEYES]

Thanks both, especially Mint.
Yes, I had forgotten that count does not work on blank cells.

Thank you also for the heads up that later versions automatically make an array formula if it returns an array. I had not realised that.