Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Count range overlap cells

Status
Not open for further replies.

N1GHTEYES

Technical User
Jun 18, 2004
771
GB
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.
 
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]
 
Code:
= 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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top