Nested array functions?
Try this (normally entered, not an array):
=SUMPRODUCT(--(ISNUMBER(MATCH(B2:G2,N1:N13,0))))
Biff
"John Beyer" wrote in message
...
Using Excel 2007 Beta 2.
I have two cell ranges (B2:G2 and N1:N13), each of which contains a list
of
non-repeating integer values in ascending order. I want a formula that
computes the number of values in the first range that appear in the second
range.
If I use scratch cells (say, AA2:AE2), I can create an array formula:
{=MATCH(B2:G2,N1:N13,0)}
that fills the scratch cells with an index for each value in B2:G2 that
matches a value in N1:N13, and #N/A for each value in B2:G2 that is absent
from N1:N13. The value I want can then be computed by:
=COUNT(AA2:AE2)
Is there a way to nest these formulas so I don't have to define scratch
cells for the intermediate step? Is there some simpler means of
computation
that I've overlooked?
|