google sheets - Create an Query Formula

Question

How can i create a query formula, if the user code in the "respostas" sheet is "1" (for example), returns which names (for this user) have less than 5 countries. In other words give me the result of the formulas F, G and H combined.

Result wanted: Nathan, Sam and Anna.

it's possible to do a query, that relates both sheets with the condition of the user?

https://docs.google.com/spreadsheets/d/1GdL5psaLKDix7282AZXGvZJUlm7rfhU2x5KlKTjp6ig/edit?usp=sharing

Thank you in advance

Answer 1

Yes, you could do it with a query combining columns F, G and H like this:

=ArrayFormula(if(A2:A="",,C2:C-vlookup(A2:A,query(filter({A2:A,COUNTIF(Respostas!B2:B,B2:B)},A2:A<>""),"select Col1,sum(Col2) group by Col1 label sum(Col2) ''"),2,false)<5))

or if you want to see the people with less than five responses:

=ArrayFormula(query(query(filter({A2:A,COUNTIF(Respostas!B2:B,B2:B)},A2:A<>""),"select Col1,sum(Col2) group by Col1 label sum(Col2) ''"),"select Col1, Col2 where Col2<5"))