Adding numbers in an Excel column with hidden rows

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!

excel-aggregate-20170605-1

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s