Search code examples
excelif-statementrowspreadsheetsubtotal

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!!!

Solution

  • If I understand correctly, you want column B to sequentially be numbers 1 to 8 for all visible rows only. Try this formula:

    =IF(MOD(SUBTOTAL(103,A$2:A2)+ROW(A2)-(COUNTA(A$2:A2)+1),8)=0,8,MOD(SUBTOTAL(103,A$2:A2)+ROW(A2)-(COUNTA(A$2:A2)+1),8))

    And populate down