Let’s assume you have a large table that you are filtering. Based on the current filter you want to work out the earliest date and the latest date. You may be surprised to learn the SUBTOTAL function can help you.

SUBTOTAL tends to be associated with the SUM function because nine times out of ten you are going to perform a SUM calculation but the SUBTOTAL function can perform many other calculations.

SUBTOTAL also has the superpower of being able to perform calculations on visible cells only.

The list we will use is shown below. It has been filtered by the state column.

Based on that filter we want to find the earliest date and the latest date.

The formulas in cells F2 and G2 provide the first and last dates respectively. The formulas in row 2 are shown in row 3.

Normally 9 is used with SUBTOTAL to SUM a range. We have used the 105 (MIN) and 104 (MAX) codes.

When there is a 10 or 11 prefix in the number used at the start of the SUBTOTAL it means the calculations only work on visible cells.

It doesn’t matter how the rows or columns are hidden, only the visible cells are used in the calculations. You can hide via the hiding feature, grouping or filtering.

When you create the SUBTOTAL function the options are listed.