# How to use FILTER to select only values repeated more than X times

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.

• What letter is the column for the names? Commented May 6, 2019 at 7:36
• For Filter is B. Commented May 6, 2019 at 7:38
• B to Names, C to jobs and D to value. Commented May 6, 2019 at 7:38

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.

• Its works!!! Thank you man :) Commented May 6, 2019 at 9:54
1. Sort your data by name (let the column for names be called B).
2. Create a column which tests whether the name is repeated at least three times. If the data has headings in row 1, the formula in the 4th row (for the third name) is

`=OR(B4=B2,B6=B4,B5=B3)`

This tests whether the name in B4 is equal to the name in two rows before it, two rows after it, or if the two adjacent names are equal. Since the data is sorted by name, the adjacent names shouldn't be equal if B4 isn't repeated.

The above formula doesn't work for the first two rows (so you should fill those two cells in by hand), but you can copy this down to the rest of the rows.

To use this column (let's call it K), you could create a new column E next to the "Value" column and put

``````=IF(K4, D4, "" )
``````

in cell E4, where `K` is the column where you put the `OR` test from point 2, then copy down and query this new column E.

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

Here is my example Sheet.

• Thanks a lot for coment Ahorn. I tried it, and i returned a FALSE value where its not duplicate. That helped me in the query: Commented May 6, 2019 at 8:27
• I did it like this "=QUERY(G2:J, "select G, AVG(I) where G is not null and J =True group by G")" but, there isnt an alternative? I mean, i want to choose for example 3 or 5 or 10, depends of the value, and i cant without editing the formula with this method. Commented May 6, 2019 at 8:28
• Sorry, I was under the impression you were querying the average of the values in column D. The method I described above should then be done next to column I. I.e. create a new column next to I where you type `IF(K4, I4, "")`, then the query is `=QUERY(G2:J, "select G, AVG(J) where G is not null group by G")`. Commented May 6, 2019 at 8:38
• Np, probably my bad. But i have one question, how i can change the number of repeated? Sometimes i need chose only if its repeated 3 times, but sometimes its 5, and sometimes is 10, its possible? Commented May 6, 2019 at 8:44
• This answer doesn't work if you want to change the number of repeats to test for. I have posted a better answer now. Commented May 6, 2019 at 9:03
• all you need is this:

``````=QUERY(QUERY({G2:I}, "where Col3 >= 3", 0),
"select Col1, avg(Col3)
group by Col1
label avg(Col3)''", 0)``````

• if you want 5 or 10 change the `3` in `"where Col3 >= 3"`

• Hey! First of all, thanks for your message! Maybe i was wrong, but what i mean, is choose the avg (as you did) depende of how many times the name is repeated, in this case, only gerard and bill need to apear, because i want to select exactly the names who works more than X times. Commented May 7, 2019 at 11:57
• in that case, remove `=` from `"where Col3 >= 3"` Commented May 7, 2019 at 11:59