A customer asked us a question a while back related to Auto Sum on a column with hidden rows. Not being an Excel wizard, I left the question in my inbox to investigate later. Fortunately, the answer arrived in an Efficiency 365 email!
If you use the AGGREGATE function on a column of numbers (instead of SUM), you can set the options to ignore hidden rows.
The link to the Efficiency 365 article is https://efficiency365.com/2016/04/06/how-to-sum-visible-cells-only/. It’s actually not much more than the picture I have shown above, but you can go to the Microsoft link at https://support.office.com/en-us/article/AGGREGATE-function-43b9278e-6aa7-4f17-92b6-e19993fa26df to learn the details of how AGGREGATE works, and how you can customize to work in your particular situation.
One Minute Office Magic 
1 Comment