3

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.

Most frequent words used

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:

The rows disappear as the rows of the filtered list do, but the results refer to all rows, not to the visible ones

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

Link to Spreadsheet

player0
  • 124,011
  • 12
  • 67
  • 124
djur
  • 304
  • 2
  • 13

1 Answers1

4

  • 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)''"))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thank you. This does the trick. Is there any way to display the results of this formula to the right side of the filtered list without hiding any of the results? – djur Apr 07 '20 at 11:12
  • if row is hidden by filter it means that row from query will be also hidden, so yes you can but it is not recommanded – player0 Apr 07 '20 at 11:23
  • I understand that. I've tried to use the same formula on a different sheet to have the information better organized but I get an error message: FILTER has mismatched range sizes. Expected row count: 20. column count: 1. Actual row count: 21, column count: 1. – djur Apr 07 '20 at 11:43
  • 1
    both ranges in FILTER needs to be of same size – player0 Apr 07 '20 at 11:53