"simal" wrote:
I have the following formula [...]:
=IF(G3="Head",SUM(INDIRECT("F"&ROW()&":F"
&SMALL(IF($G$2:$G$1000="Head",ROW($G$2:$G$1000) ),
COUNTIF($G$2:G3,"Head")+1)-1)),"")

Essentially, it should boil down to:
=IF(G3="Head",SUM(F3:F14),"")
for example. However, since the range reference is in
text I have added the INDIRECT so that it reads
SUM(INDIRECT("F3:F14")).
On its own, this works fine but when incorporated into
my long function above it doesn't work.

I assume you remembered to array-enter the formula by pressing
ctrl+shift+Enter instead of just Enter.

When I do that, I agree that the full =IF(...) formula does not work.

Using the Evaluate Formula operation (which is not always reliable, FYI), we
see that the root cause of the problem is: ROW() is replace with an
__array__ {3} instead of the simple integer 3.

There is no good reason for that; it is a defect in Excel, IMHO.

It does not happen in the following array-entered formula, for example:

=IF(G3="Head","F"&ROW()&":F"
&SMALL(IF($G$2:$G$1000="Head",ROW($G$2:$G$1000) ),
COUNTIF($G$2:G3,"Head")+1)-1,"")

The problem arises when that string expression is part of an INDIRECT
parameter.

When I have encountered this before, I worked around it by replacing, in
your case, ROW() with MIN(ROW()).

However, you can avoid the problem altogether by simplifying the
implementation, which would be prudent anyway.

It appears that you intend to put the formula into some cell in row 3 and
copy it down the column in parallel to the data in column F and G.

In that case, you can avoid the use of ROW() by writing the following
array-entered formula (remember to press ctrl+shift+Enter instead of just
Enter):

=IF(G3="Head",SUM(F3:INDIRECT("F"
&SMALL(IF($G$2:$G$1000="Head",ROW($G$2:$G$1000) ),
COUNTIF($G$2:G3,"Head")+1)-1)),"")

As you copy the formula down the column, Excel changes F3 in the same way
that "F"&ROW() would be changed.

Moreover, you can use INDEX instead of INDIRECT. That is a "good practice"
whenever reasonable because INDIRECT is a volatile function. Consequently,
all of your formulas of this form are recalculated every time Excel
recalculates anything in the workbook; for example, whenever any cell in any
worksheet is edited.

INDEX is not a volatile function [1]. So you can write the following
array-entered formula (press ctrl+shift+Enter instead of just Enter):

=IF(G3="Head",SUM(F3:INDEX(F:F,
SMALL(IF($G$2:$G$1000="Head",ROW($G$2:$G$1000)),
COUNTIF($G$2:G3,"Head")+1)-1)),"")

Finally, I think you can simplify things significantly as follows (again,
array-entered):

=IF(G3="Head",SUM(F3:INDEX(F:F,
MIN(IF(G4:$G$1000="Head",ROW(G4:$G$1000)))-1)),"")

All of these formulas make the same assumption, to wit: G1000="Head", and
the formula is copied down no further than row 999. Otherwise, SMALL
returns a #NUM error, and the MIN formula returns an incorrectg result.

If that assumption is incorrect, here is one way to correct the formulas
(again, array-entered):

=IF(G3="Head",IF(ROW(G3)=ROW($G$1000),F3,
SUM(F3:INDEX(F:F,SMALL(IF($G$2:$G$1000="Head",
ROW($G$2:$G$1000)),COUNTIF($G$2:G3,"Head")+1)-1))),"")

or

=IF(G3="Head",IF(ROW(G3)=ROW($G$1000),F3,
SUM(F3:INDEX(F:F,MIN(IF(G4:$G$1000="Head",
ROW(G4:$G$1000)))-1))),"")


-----
[1] INDEX is not a volatile function. But it causes Excel to recalculate
some formulas that use INDEX when the workbook is opened, if it is in
Automatic calculation mode.