How to sort Google Sheet by number of empty cells per row?

Question

In my sheet, there are 5 columns of data. Some rows are fully filled and some rows have some empty cells. Now I want to sort my sheet from most-filled row to least-filled row. How can I do it?

Answer 1

Simply use =COUNTA(range) in a new column, and then use that column to sort.

  X      X      X      X      X      =COUNTA(A1:E1)
  X      X             X      X      =COUNTA(A2:E2)
  X                                  =COUNTA(A3:E3)
  X             X             X      =COUNTA(A4:E4)
  X             X                    =COUNTA(A5:E5)
  X                           X      =COUNTA(A6:E6)

Results in

  X      X      X      X      X      5
  X      X             X      X      4
  X                                  1
  X             X             X      3
  X             X                    2
  X                           X      2

Then use column F to sort your data.

Answer 2

you will need +1 column to generate sorting sequence used in =QUERY which will return your data

assuming your dataset somehow looks like:

paste this formula in cell F1:

=ARRAYFORMULA(IF(LEN(A:A&B:B&C:C&D:D&E:E);
              MMULT(--(LEN(A:E)>0);
              TRANSPOSE(SIGN(COLUMN(A1:E1))));
              IFERROR(1/0)))

and then paste this formula wherever you need to:

=QUERY(A1:F; "select A, B, C, D, E where F is not null order by F desc"; 0)

---

---

if overall visual requires so, you can easily hide the whole F column: