Nested array functions?
 

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?

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?

Nested array functions?
 

Or, simply:

=SUMPRODUCT(COUNTIF(N1:N13,B2:G2))

Or, the shortest formula (array entered):

=SUM(COUNTIF(N1:N13,B2:G2))

Biff

"Biff" wrote in message
...
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?