How to apply IF statement condition to subtotal to visible rows in Excel

I am currently using the function below to drag and fill down a column to create a repeated sequence of numbers 1-8.

=IF(B1<8,B1+1,IF(B1=8,B1-7))

I would like to only apply this to visible rows. I have been playing around with subtotal, offset, sumproduct, but the examples I am finding are not using the IF statement so I am having trouble making it work for my scenario.

Any help is appreciated!!!