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().