Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula w/ COLUMN & MATCH FUNCTIONS | Excel Discussion (Misc queries) | |||
large formula question - Max nested functions | Excel Worksheet Functions | |||
Nested IF and MID functions | Excel Worksheet Functions | |||
Array functions ARGHH! | Excel Worksheet Functions | |||
Array Functions from Alan Beban | Excel Worksheet Functions |