I want to set column E by Ascent order but I want rows that contain Zeros to remain right at the bottom. is this possible with the Query function?
Thanks in advance!
2 Answers
Try:
={query(B2:E,"where E>=1 order by E",0);query(B2:E,"where E=0",0)}
If you need to sort the second query, just add the relevant order by.
Another method:
=SORT(B2:E,IF(E2:E>0,E2:E,9^9),1,1,0)
This reads, "Sort B2:E in ascending order by E after replacing zeros with 9^9, with a secondary sort by B descending (in case any blank entries make it into E)."
Since 9^9 is a number much higher than any that would normally appear in E, zeros replaced with that number will sort to the bottom.
If you like, you can easily include cumulative sorting by the other columns as well:
=SORT(B2:E,IF(E2:E>0,E2:E,9^9),1,1,0,2,1,3,1)
Related