I think you need to absolutely reference the cells in the offset formula..

=OFFSET(Sheet1!$C$6,0,1,1,COUNTA(Sheet1!$6:$6)-2)

if you don't want to and it must be relative, then you need to enter the Subtotal formula in the same row... but then you will need to change 6:6 to a defined name that doesn't include the cell you are entering the subtotal formula in.