Shouldn't it just be

=SUM((IF((ISERROR((FIND($B751,$E4:$BL4)))),0,1)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Philip Hunt" <Philip wrote in message
...
PROBLEMS WITH USING TEXT, INDIRECT AND ADDRESS WITHIN AN ARRAY FORMULA
Basic problem
The array formula {=SUM((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))} gives
me
my expected value of 3. I need, though, to have a formula of this format
across 60 columns x 2000 rows =120,000 cells. The search value changes
for
each row, and the row in the search area changes for each column. There
are
therefore in fact 120,000 similar formula calculations required, but they
are
slightly different in each case. I have therefore tried to turn this into
an
array formula, storing it with a name and then just copying =[Name] to
each
of the other 119,999 cells. I cannot though get the function to work even
once and I am at my wit's end after five evenings of trying.
As an extension of the above what I really want is not the SUM but in fact
the 'string' of zeroes and ones that the arrayed ISERROR produces to be
placed in the cell as a text string. I have tried
=TEXT(((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))," 0") but all I get is
zero,
rather than the desired
"0000000000000000100000000000000010000000001000000 000000000000". I have
also
tried it with the format string being a string of 60 zeroes, but that
produces the same 'bad' result.

Facts established towards 'building of formula'
{=SUM((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))} works as I want it to.
(INDIRECT("$B"&ROW())) gives me the result B751 when placed in row 751.
=(ADDRESS((INDIRECT("R249C"&COLUMN(),FALSE)),5,4,1 )&":"&ADDRESS((INDIRECT("R249C"&COLUMN(),FALSE)),6 4,4,1))
gives me the text answer E4:BL4 when placed in the column that has the
number 4 in its cell in row number 249.

BUT
when placing these 'tested' elements in their appropriate places in the
formula, in replacement of B751 and E4:BL4, as follows -
{=SUM((IF((ISERROR((FIND((INDIRECT("$B"&ROW())),(A DDRESS((INDIRECT("R249C"&(COLUMN(),FALSE)),5,4,1)& ":"&ADDRESS((INDIRECT("R249C"&COLUMN(),FALSE)),64, 4,1)))))),0,1)))}
the formula does not work.

I look forward to comments and advice from the user community.
Best regards
Philip
Medina, Kwinana, Perth, Western Australia