MrExcel's Learn Excel #350 - Total Visible Rows

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Nov 12, 2009.
There is another way to use the AutoSum icon in the standard toolbar. This alternate method will show you the totals of the visible cells, ignoring cells in hidden rows. Episode 350 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
maxresdefault.jpg


Transcript of the video:
Hey, welcome back to the MrExcel podcast, I'm Bill Jelen.
Continuing the series of podcasts with new and better tricks since I've written the book, one trick that is in the book is the AutoSum icon.
If you select the series of blank cells underneath columns of numeric data, and hit the Σ, the sigma up in the toolbar, Excel will automatically add a SUM function to all of those cells.
Now another tip is the AutoFilter feature.
If you turn on Data, Filter, AutoFilter, you have a drop-down next to every heading, and you can choose to see the records for just a particular customer.
Now, here's the amazing thing, once the data is already filtered, and you're seeing just a subset of the records, if you repeat the AutoSum trick, instead of getting a SUM formula, you will get a subtotal formula.
And the subtotal is set up to only show you the value of the visible rows, so this particular customer buy 2600 units, if I choose another customer and scroll down, and we'll see that that customer purchased 29100 units.
That subtotal function is pretty hard to remember, =SUBTOTAL(9, and then the range, but using the AutoSum icon once your data is already been filtered is a great way to very easily create that formula.
Hey, thanks for stopping by, we'll see you tomorrow for another podcast from MrExcel!
 

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top