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)),"")