So we are opening up school again and I am trying to get some data from a survey sorted. I have two sheets, so I will focus on one right now. I have a formula that works but doesn't match the other sheet. So one of the formulas is wrong.

I want to count online/in-person, then grade level. But I'd also like to break it down by school. I have attached the sheet with the formulas I am using for two, but can't figure out the last one. https://docs.google.com/spreadsheets/d/1dcmPJZefVGIoEfx7_zbq4SaGzG9EgP8YRwEM3MQFvto/edit?usp=sharing

  • Welcome. What are the names "Thomas", "Lincoln", etc? Are they the names of the schools? If so why line 19 has both "Lincoln" and "Washington"? – marikamitsos Feb 13 at 20:34
  • they are the name of the school. The parents were allowed to enter in the school for each child. In case some were middle/high. – Kevin Wendland Feb 13 at 21:51
  • Why line 19 has both "Lincoln" and "Washington"? Is that a mistake? Do the parents fill-in a form? What is your work-flow? – marikamitsos Feb 13 at 22:06
  • yeah we do have a few parents that will send their kids to different schools, not a lot but I just want to account for it – Kevin Wendland Feb 13 at 23:26
  • Thank you for replying, but could you please answer the following? Is there a mistake in line 19? Do the parents fill-in a form? What is your work-flow? Knowing these, it will probably be easier to help you. – marikamitsos Feb 13 at 23:34

Try this formula. I created a new sheet to view the results - that's why the ranges reference "Sheet1".

=query({Sheet1!A2:C21;Sheet1!A2:A21,Sheet1!D2:E21;Sheet1!A2:A21,Sheet1!F2:G21},"select Col1,Col2,Col3,COUNT(Col3) where Col2 <>'' group by Col1,Col2,Col3 label COUNT(Col3) ''")


(Note: the header line is entered manually)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.