Hi everybody,

once more INDIRECT is producing a behavior I cannot decipher. Problem:

Cells A1:A18 contain various numbers, some of which are 0. A1 is always
a non-zero number. Consecutive zeros are not expected.

In B2:B17 I want a formula, which will produce the sum, next to each 0,
of the numbers since the last 0. This is an older question in the
group, for which Bob Phillips had supplied a solution, involving two
columns. I was trying to see if I can do it in one column. Here is what
I have in B2: (array formula)

{=IF(A2=0,SUM(N(INDIRECT("A"&MAX(1,ROW($A$1:A1)*($ A$1:A1=0))&":A"&ROW()))),"")}

This formula produces 1 for the first sum and 0 for all other sums. I
would understand it better if it produced #VALUE! instead.

When I extract the argument to INDIRECT and enter it as a separate
formula in C2,

{="A"&MAX(1,ROW($A$1:A1)*($A$1:A1=0))&":A"&ROW() }

Then the formula in D2 works properly:

=IF(A2=0,SUM(INDIRECT(C2)),"")

Why is this happening? INDIRECT often behaves strangely and I have not
yet found a consistent framework for when it does and when not, despite
various discussions in the NG. Can you enlighten please?

TIA

Kostis Vezerides