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
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"))
Hello i just need a query formula.(=query) that way i can write in the app.
I saw this solution link but the issue is that i'm trying to convert to what i need but i think this is the right way to start. Unfortanetly the file is not attached to try to understand what they did.
How to use ARRAYFORMULA in query where clause?
TY
