Hi,
One of the few ways I know of which makes it possible to indicate whether rows are visible or not in Excel is the following:
1) Fill a helper column (A) with 1's.
2) Put the formula =SUBTOTAL(2,A1) in cell B1 and copy it down.
3) When an auto filter is applied to content in the sheet, the formula in 2) will result in 1's for rows that are visible and 0's for rows that are hidden.
4) Thus it is possible to incorporate this into the criteria for formulas that don't have a built-in possibility to ignore hidden rows (like the AGGREGATE function).
I would like to see an alternative solution to this with a dynamic array formula with a spill range instead of having to copy the formula down, preferably also without using a helper column.
Does anybody know how to do this, if it's possible?
Best regards,
Marbleking
Bookmarks