Applying formula to visible cells only (using filters) on Google Sheets

Question

Brief: I am using a formula to retrieve the information relative to the most frequently used words in a list. I am trying to make it work together with a filter in order to narrow down the results.

Parallel to this, I am filtering the results of a table based on one category (i.e. country) using filters. However, when I narrow down the results using the filter, I encounter two problems:

1. The filter only hides the rows that do not contain the country of choice, so the results I get are exactly the same. How can the results of this formula be narrowed down based on the visible cells?

2. I only manage to see all the results when I place the formula below the data, as shown on the screenshot below. Is there a way to force-show the results to the side of the filtered list?

Here's the screenshot:

Here's the spreadsheet I have created to work on a solution:

Link to Spreadsheet

Answer 1

• un-filter your B:C range

• paste in D2 and drag down this formula: =SUBTOTAL(103, B2)

• hide column D

• use this formula:

---

=ARRAYFORMULA(QUERY(TRANSPOSE(SPLIT(QUERY(FILTER(B3:B21, D3:D21=1)&",",,99^99), ",")), 
 "select Col1,count(Col1) 
  group by Col1
  order by count(Col1) desc
  limit 5
  label count(Col1)''"))