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.