Menu
I have a table that has 20 rows and the table un-hides the amount of rows specified in the cell above my table. so if I put in 5 into the cell, 5 of the 20 rows will be un-hidden. So now I would like to get the MAX of the say 5 cells that are now unhidden. How would I do this?
551
Just organize your data in table (Ctrl + T) or filter the data the way you want by clicking the Filter button. After that, select the cell immediately below the column you want to total, and click the AutoSum button on the ribbon. A SUBTOTAL formula will be inserted, summing only the visible cells in the column.
If you need to find the highest or lowest three entries in a filtered list you can use the AGGREGATE function to find them. The AGGREGATE function was introduced in Excel 2010. It works in a similar way to SUBTOTAL It has the ability to make calculations based only on visible cells.
Like a lot of useful actions in Excel, there is a keyboard shortcut for this. After highlighting the entire range, press ALT + ; and only the visible cells will be selected. Once the visible cells have been selected, you can now copy just those cells.
=SUBTOTAL(104,RANGE)
104 will give you the max of the unhidden cells. If you replace 104 with 4 it will operate as the normal MAX function UNLESS you are filtering the rows using the built in Excel table function.
185
Perhaps
=SUBTOTAL(104,your_range)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
