the SUBTOTAL/OFFSET approach isn't great, from a performance angle, so I would suggest that instead, for ultimate flexibility, you add a new field to your source to flag row visibility, e.g

=SUBTOTAL(2,[Dates])
this will return 1 if the row is visible (via filtering), 0 if filtered out

you can then add this new field to your SUMIFS with a fixed criteria of 1 -- this will ensure you only ever include visible rows