Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Hi,
From cells A1 through L50 I have numbers. A particular row let's say A1:A50 might/would have some numbers repeating. Same for other rows in the range A1:L50. In column M from row 2 to row 10, I have some numbers (This list in column M has no repeating numbers) . I want to do 2 kinds of calculations. a) I want to know the count of INTERSECTION of A1:L1 and M2:M10. That is does A1:L1 range has all the numbers present in M2:M10 or only 8 of the numbers of M2:M10 and so on. I used the formula =SUM(IF(ISERROR(MATCH($M$2:$M$10,A1:L1,0)),0,1)) Please note I array entered the above formula in cell N. Then I copied this formula down to N50 so that I can know the same for each row in the range A1:L50 Problem with the above formula is that A1:L1 would have duplicates ( and same for other rows in the range), so am getting an incorrect answer in using the above formula. How to weed out the duplicates and pass a unique range of numbers within A1:L1. Or is there a better method /approach to it? (Please note I prefer a formula /non-programmatic solution). b) I want to know the NUMBER of ROWS within the range A1:L50 which has the number in M2, the same for M3 and so on till M10. So, in O2 I entered the following Array Formula to find number of rows in the range A1:L50 which has the number in M2. =SUM(IF(ISERROR(MATCH(M2,$A$1:$L$50,0)),0,1)) Im getting a Zero. I went in to Evaluate formula mode (Have 2002) but ... I know that the Look_up array within the Match function has to be a One Row / Column Range.. but doesnt using an Array formula mean that Excel will break the range A1:L50 in the Lookup_array in to single row ranges and then do such evaluations for each row. Isnt an Array Formula is supposed to do that kind of things? Please guide me in resolving b) as well. Thanks a lot, Hari India |
#2
![]() |
|||
|
|||
![]()
a]
N1, copied down: =SUMPRODUCT((A1:L1<"")/COUNTIF(A1:L1,A1:L1&""),--ISNUMBER(MATCH(A1:L1,$M$2:$M$10,0))) b] O2, copied to O9... =SUMPRODUCT(--(COUNTIF(INDIRECT(ADDRESS(ROW($A$1:$A$50),COLUMN(A $1))&":"&ADDRESS(ROW($A$1:$A$50),COLUMNS(A$1:L$1)) ),M2)=1)) Investigate whether you can replace INDIRECT(ADDRESS(...),ADDRESS(...)) with OFFSET(). Hari Prasadh wrote: Hi, From cells A1 through L50 I have numbers. A particular row let's say A1:A50 might/would have some numbers repeating. Same for other rows in the range A1:L50. In column M from row 2 to row 10, I have some numbers (This list in column M has no repeating numbers) . I want to do 2 kinds of calculations. a) I want to know the count of INTERSECTION of A1:L1 and M2:M10. That is does A1:L1 range has all the numbers present in M2:M10 or only 8 of the numbers of M2:M10 and so on. I used the formula =SUM(IF(ISERROR(MATCH($M$2:$M$10,A1:L1,0)),0,1)) Please note I array entered the above formula in cell N. Then I copied this formula down to N50 so that I can know the same for each row in the range A1:L50 Problem with the above formula is that A1:L1 would have duplicates ( and same for other rows in the range), so am getting an incorrect answer in using the above formula. How to weed out the duplicates and pass a unique range of numbers within A1:L1. Or is there a better method /approach to it? (Please note I prefer a formula /non-programmatic solution). b) I want to know the NUMBER of ROWS within the range A1:L50 which has the number in M2, the same for M3 and so on till M10. So, in O2 I entered the following Array Formula to find number of rows in the range A1:L50 which has the number in M2. =SUM(IF(ISERROR(MATCH(M2,$A$1:$L$50,0)),0,1)) Im getting a Zero. I went in to Evaluate formula mode (Have 2002) but ... I know that the Look_up array within the Match function has to be a One Row / Column Range.. but doesnt using an Array formula mean that Excel will break the range A1:L50 in the Lookup_array in to single row ranges and then do such evaluations for each row. Isnt an Array Formula is supposed to do that kind of things? Please guide me in resolving b) as well. Thanks a lot, Hari India |
#3
![]() |
|||
|
|||
![]()
Hi Aladin,
Your formula works nicely for both a) and b). Thnx a lot. I have a doubt. How is it that in a) Match has a range (not a cell) for Lookup_value but still we dont need to Array enter the formula for getting correct answers. Thanks a lot, Hari India "Aladin Akyurek" wrote in message ... a] N1, copied down: =SUMPRODUCT((A1:L1<"")/COUNTIF(A1:L1,A1:L1&""),--ISNUMBER(MATCH(A1:L1,$M$2:$M$10,0))) b] O2, copied to O9... =SUMPRODUCT(--(COUNTIF(INDIRECT(ADDRESS(ROW($A$1:$A$50),COLUMN(A $1))&":"&ADDRESS(ROW($A$1:$A$50),COLUMNS(A$1:L$1)) ),M2)=1)) Investigate whether you can replace INDIRECT(ADDRESS(...),ADDRESS(...)) with OFFSET(). |
#4
![]() |
|||
|
|||
![]()
MATCH is capable of returning an array of results. That we don't need
control+shift+enter is SumProduct's doing. Hari Prasadh wrote: Hi Aladin, Your formula works nicely for both a) and b). Thnx a lot. I have a doubt. How is it that in a) Match has a range (not a cell) for Lookup_value but still we dont need to Array enter the formula for getting correct answers. Thanks a lot, Hari India "Aladin Akyurek" wrote in message ... a] N1, copied down: =SUMPRODUCT((A1:L1<"")/COUNTIF(A1:L1,A1:L1&""),--ISNUMBER(MATCH(A1:L1,$M$2:$M$10,0))) b] O2, copied to O9... =SUMPRODUCT(--(COUNTIF(INDIRECT(ADDRESS(ROW($A$1:$A$50),COLUMN(A $1))&":"&ADDRESS(ROW($A$1:$A$50),COLUMNS(A$1:L$1)) ),M2)=1)) Investigate whether you can replace INDIRECT(ADDRESS(...),ADDRESS(...)) with OFFSET(). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Help with array formula | Excel Worksheet Functions | |||
How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) | |||
Vlookup, Index & Match | Excel Worksheet Functions |