Using SUM(INDIRECT())
 

I have the following formula that I'm still in the process of developing:

=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'm wondering whether it's because it's an array function as using the Evaluate Formula tool shows that the formula gets all the way to SUM(INDIRECT({"F3:F14"})) which returns SUM(INDIRECT({#VALUE!})), which in turn returns SUM(INDIRECT(0)), which equals 0.

Can anyone help with a way of getting the sum range in a format that the SUM function will understand?

Thanks.

Using SUM(INDIRECT())
 

"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.

Using SUM(INDIRECT())
 

Errata.... I wrote:
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.

The MIN formula returns an incorrect result if G1000="Head" and the formula
is copied down through row 1000. Otherwise, the MIN formula returns a
#VALUE error.


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

If is probably not a good idea to reference G4 because of Excel's treatment
of G4:$G$1000 if the formula is copied down through row 1000.


I wrote:
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))),"")

Hogwash!

If you cannot abide by the assumptions above, try one of the following
(remember: array-entered by pressing ctrl+shift+Enter instead of just
Enter).

(Also note the simplification of the SMALL expression.)

For Excel 2007 and later:

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

For Excel 2003 and earlier:

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