MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel 2024: Total the Visible Rows

February 20, 2024 - by

Excel 2024: Total the Visible Rows

After you've applied a filter, say that you want to see the total of the visible cells.

Select the blank cell below each of your numeric columns. Click AutoSum or type Alt+=.

A filtered data set is shown. The first blank row below the data is row 565. Select cells E565:H565 and click AutoSum.
A filtered data set is shown. The first blank row below the data is row 565. Select cells E565:H565 and click AutoSum.

Instead of inserting SUM formulas, Excel inserts =SUBTOTAL(9,...) formulas. The formula below shows the total of only the visible cells.

Instead of inserting =SUM functions, Excel inserts =SUBTOTAL functions with a first argument of 9. This function totals only the visible rows.
Instead of inserting =SUM functions, Excel inserts =SUBTOTAL functions with a first argument of 9. This function totals only the visible rows.

Insert a few blank rows above your data. Cut the formulas from below the data and paste to row 1 with the label Total Visible.

The Total Visible formulas from row 565 are cut and pasted above the data.
The Total Visible formulas from row 565 are cut and pasted above the data.

Now, as you change the filters, even if the data fills up more than one full screen, you will see the totals at the top of your worksheet.

Thanks to Sam Radakovitz on the Excel team for Filter by Selection - not for suggesting Filter by Selection, but for formalizing Filter by Selection! Thanks to Taylor & Chris in Albuquerque for the Over/under technique.

This article is an excerpt from MrExcel 2024 Igniting Excel

Title photo by Egor Myznik on Unsplash