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

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?

• can you share a copy of your sheet? – user0 Sep 1 '18 at 17:29
• did I? was my solution somewhat not working for you? – user0 Sep 2 '18 at 18:44

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.

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: 