Need the inverse of counting visible rows

[jtyoder]

I need an Excel formula that will allow me to provide a starting cell, then calculate the row number of a cell that is 'n' rows after the starting cell. The rub is that my table is filtered so I can't do a simple addition. I know you can use SUBTOTAL to count only the visible cells, but I don't want to count cells I want to add a known number to the row of my starting cell to end up with the row number that is 'n' visible rows after the starting cell.
As an example, if my table has 10 rows and rows 2, 3, and 4 are filtered out, and I want the fifth visible row after row 1, I need a formula that would return 9 (1 + 5 visible rows = 9).
I can't seem to wrap my head around how to do this.
Any help would be appreciated.

 

[Chihiro]

Follow steps in link.
https://chandoo.org/wp/exclude-hidden-rows-from-totals/

Using SUBTOTAL(102,CellRef) you can check if cell/row is visible or not.

Then you just need to add another helper column to do running total on SUBTOTAL(). Ex: SUM($B$1:B1) Copy down.

Finally do SUMIF
=SUMIF(C:C,6,A:A)

See attached.

There are other methods, but for ease of maintenance, I'd recommend this approach.