Google-sheets – How to use FILTER to select only values repeated more than X times

filterformulasgoogle sheetsgoogle-sheets-query

I am trying to use the filter function to select from 2 columns, only the values repeated. I will give you an example:

My column is like:

Names  Jobs  Value
-
Gerard  player  4
Gerard  player  2
Bill    player  5
Bill    painter 4
Bill    player  4
Bill    player  3
Gerard  painter 3
Helena  player  3
Helena  player  2
Mary    player  3

I have this Query

=QUERY(G2:I, "select G, AVG(I) where G is not null group by G")

And I can filter my data to get average for the values for each name. Okay, it's fine.

Now, I want for this Query, select only if I the name is repeated 3 times or more. I tried a lot of things with the Query, so, I deserved it.

Then, I tried to did it with my filter formula. It's like that:

=FILTER(B1:D,DASHBOARD!F24=C1:C)

I am using a dashboard to select the Job, to return the average, but the individual value for just one job, or when a lot of names are repeated 20 times, for example, it's not fair to show who just came one time.

So, to resume, I won't change my filter column (or query, if someone knows), to select the names only if the value is higher than X (maybe can be 5 or 10, idk).

I don't know if something is not clear, I am not good in English, but if u need, I can clarify anything.

Best Answer

Create a column which tests whether the name is repeated at least x amount of times. The formula in the 2nd row is

=COUNTIF(B:B,B2)>=$L$1

where the names are in column B, and L1 is where you have input the number of times the name should have been repeated. The data doesn't need to be sorted beforehand for this to work.

To use this column (let's call it K), you could create a new column J next to column I, and put

=IF(K2, I2, "" )

in cell J2, then copy down and query this new column J, as follows:

=QUERY(G2:J, "select G, AVG(J) where G is not null group by G")

There could be an alternative method for using column K, such as within the QUERY function itself.

Here is my example Sheet.

Related Topic