SUM causes strange error

[JM]

I've come across a strange problem. When I invoke the
following array formula, I get exactly what I expected, a
3 element array.
However, when I wrap the same expression with SUM(),
Excel complains that the formula contains an error and
highlights the second COUNTA and reports #NUM! in the
cell. When I replace the 2nd and 4th COUNTA(A:A) with
the explicit number (191 for my sheet), everthing works
fine. Can someone help? I need to keep the number of
rows a variable.


=SMALL(
INDIRECT("j2:j"&
SMALL(
IF(
IF( INDIRECT("j2:j"&COUNTA(A:A))<>"",
ROW(INDIRECT("j2:j"&COUNTA
(A:A))),"")
< CELL("row",J2),
"",
IF( INDIRECT("j2:j"&COUNTA(A:A))<>"",ROW
(INDIRECT("j2:j"&COUNTA(A:A))),"")
),20)
),{1;2;3})

 

[JE McGimpsey]

You've reached the maximum number of nested functions. Functions can
only be nested 7 deep. Wrapping with SUM() would cause the COUNTA() to
be the 8th.

You'll either have to rearrange it or break it up.

http://cpearson.com/excel/nested.htm

may be of help.

 

[Frank Kabel]

Hi
one idea would be the replacement of
INDIRECT("j2:j"&COUNTA(A:A))
with a defined name

 

[JM]

Thanks to both you and Frank (for the suggested
substitution). I was going nuts with this one.

Jim