Google sheet select rows of paid where open and paid are there and open only and paid only

There are probably several ways to construct an answer to your question. Consider the following as one approach.

Paid invoices with an Open entry

=query({query({A2:C},"select Col1,Col2, Col3 where Col1 is not null and Col3 = 'Paid' order by Col1")},"select Col1, Col2, Col3 where Col1="&JOIN(" or Col1=",query({A3:C},"select Col1 where Col1 is not null and Col3 = 'Open' order by Col1"))&""&" label Col3 'Paid and Open' ")

Open invoices with no Paid entry

=query({query({A2:C},"select Col1,Col2, Col3 where Col1 is not null and Col3 = 'Open' order by Col1")},"select Col1, Col2, Col3 where Col1<>"&JOIN(" and Col1<>",query({A3:C},"select Col1 where Col1 is not null and Col3 = 'Paid' order by Col1"))&""&"")

Paid invoices with no Open entry

=query({query({A2:C},"select Col1,Col2, Col3 where Col1 is not null and Col3 = 'Paid' order by Col1")},"select Col1, Col2, Col3 where Col1<>"&JOIN(" and Col1<>",query({A3:C},"select Col1 where Col1 is not null and Col3 = 'Open' order by Col1"))&""&"")

Comments:

  • each answer replies on a nested queries
  • the initial query includes where clause for the default Status value
  • the where clause in the second query relies on a JOIN function to include or exclude invoices with a given Status
  • the JOIN function uses a third query to provide the array results for the required Status.
  • the JOIN function also includes AND/OR that operates on the where clause

snapshot

Credit: @benfromredant Using query to find the entries in one column which are not in the other