Custum subtotal formula for autofiltered data

[1eyedjack]

I have an autofiltered table of data, header row 5, within which column F contains numerical values, column E contains a cumulative total of visible values in column F and column D contains a cumulative total of all values visible or hidden in column F.
Thus, Cell E10 contains the formula "=subtotal(9,$F$5:F10)" and Cell D10 contains the formula "=sum($F$5:F10)"

I require column C to contain a sort of hybrid cumulative total of column F that follows the following rule: All hidden rows between row five and the first visible row are to be included in the cumulative totals, but all other hidden rows are to be excluded.

It may require a UDF, and if so I can live with that, but if there is a solution that avoids the use of a UDF I would prefer it. I am happy to use additional columns and defined names for intermediate stages of calculation.

Any help greatly appreciated, please.

If I could just have a formula that returns the row number of the first visible row, then the solution will be trivial.

[arlu1201]

Your question will be best explained with the help of a sample workbook. Why dont you attach it?

[1eyedjack]

Hi thanks for the suggestion.

I think I have a solution. It is really kludgy, and uses a UDF, but for what it is worth I post it here.

I have not flagged the original solution as "SOLVED" because I feel that there must be a better way. If nothing is suggested in a couple of days I hope I remember to come back and flag it solved.