Welcome to Qi-U Community for programmer and developer-Open, Learning and Share

Categories

0 votes
181 views
in Technique[技术] by (31.9m points)

google sheets - Create an Query Formula

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

Please log in or register to answer this question.

1 Answer

0 votes
by (31.9m points)

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))

enter image description here

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to Qi-U Community for programmer and developer-Open, Learning and Share
...