There are multiple options...

• A pivot table
• The subtotal function

...but in the end, there isn't a one formula that could do it that I know of.

Actually, the best way to represent what you want is to attach a sample workbook with a before and after in-lieu of a picture.