I have a Google sheet that list of files with a corresponding box number(Col1)labeled "Box Number". I want to be able to gather all files by each unique box number and then return the min and max number(Col5) labeled "Registry ID 1" of each box. For example, Box 1 Min is 1 and Max is 10, Box 2 Min is 11 and Max is 20, and so on. If I haven't explained it well enough or you need more info let me know.

Box Number Registry ID 1
1 1
1 2
---------- --------------
2 3
2 4

Link to sheet

  • Please edit the table in the question and insert a reasonable amount of representative sample data, including the values in the two columns mention, together with another table that shows your exact manually entered desired results from that sample data. Apr 28, 2023 at 21:08

1 Answer 1


Choose Insert > Sheet and put this formula in cell A1 of the new sheet:

  'Boxes Returned to NPRC'!A1:E, 
  "select A, min(E), max(E) 
   where E is not null 
   and E <> 'SKIP' 
   group by A", 

See query().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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