Google Sheets - Exclude Hidden or Filtered Rows

When using common math functions such as SUM, COUNT, or AVERAGE, the functions work on all of the values in the range regardless of whether they are hidden or filtered. If you do not want your function to pick up filtered or hidden rows, this video will show you two solutions using the SUBTOTAL function to ignore hidden and/or filtered rows.

🕒 Timestamps:
0:00 No filter or hidden rows
0:53 Filtered rows
2:27 Hidden rows
3:14 Next videos - Automate with macros

Learn more from Prolific Oaktree:

#googlesheets #spreadsheet #SUBTOTAL

[BartomiejGawron]

And if that's not enough, you can use something like this:
=SUBTOTAL(9;J532)/J532
to mark filtered columns as 1
unfiltered columns will be 0
(of course, there should be numeric values =/=0 in column J)

[tejassalway]

anyone know how to exclude filtered numbers when using quartile function?

[jimbojacobe]

what is the equivalent of excel selecting visible cells only and excluding the hidden cells in a multiple column and row?

[delinquense]

Great, but is there any way to filter out hidden columns for functions like transpose. i.e. I have 4 columns, but 2 are hidden. In this case, I would want only 2 rows. WIthout using appscript of course.

[knitterknerd]

What about non-math functions? I have an Excel spreadsheet that chooses a random item from unfiltered table rows. Long story short, it uses the AGGREGATE formula to exclude hidden rows when making its choice. Of course, AGGREGATE isn't available in Google Sheets, so I need a workaround.

[eljinet]

So on Google Sheets I have this:

=iferror(FILTER('RAW DATA 2'!$B$2:$G, 'RAW DATA 2'!$B$2:$B>=$B$1, 'RAW DATA 2'!$B$2:$B<=$B$2), ""), the formula is working. However, I would like to skip a column from the range, my range goes from B2:G, I would like to skip column D from the range, how can I do it?

Thanks in advance.

[enkeegantumur]

How to sum excluding hidden columns? Subtotal109 is not working.

[eg]

can you filter out the information in a row but still have the row show? Also does this work with PERCENTIF?

[paulchristlewithtrealty]

How can I delete all of the hidden rows? Or copy / paste all rows that are not hidden to create a new sheet?

[sebacatana]

But this is only for sum. So the title of the video should be ”Exclude Hidden or Filtered Rows when using the SUM function”