Is it possible to add an IF to a SORT function?

Question

So I have data like this:

  A     B    C
----------------
Name #  #  TI:ME
----------------
Name 1  2  08:42
Name 2  3  09:23
Name 3  0  12:54
Name 4  1  14:28
Name 5  3  07:19
Name 6  2  07:16
Name 7  0  15:28
Name 8  1  13:06

Right now, I'm able to use SORT to sort it by Column B and the Column C, but what I want is for it to sort by Column B first, and then sort Column C by ASCENDING IF the entry in Column B = 3, and if Collumn B is 2, 1, or 0, to sort Column C in DESCENDING.

So it'd look a little something like this:

  A     B    C
----------------
Name #  #  TI:ME
----------------
Name 5  3  07:19
Name 2  3  09:23
Name 1  2  08:42
Name 6  2  07:16
Name 4  1  14:28
Name 7  0  15:28
Name 8  0  13:06
Name 3  0  12:54

Is this possible?

Answer 1

The following formula will give you the expected outcome.

Formula

=SORT(
   {
     QUERY(A1:C8,"SELECT * WHERE B = 3 ORDER BY C ASC");
     QUERY(A1:C8,"SELECT * WHERE B <> 3 ORDER BY C DESC")
   },
   2, 
   FALSE
 )

// formula with error handling
=SORT(
   IF(COUNTIF(B2:B9, 3)>0,
     IF(COUNTIF(B2:B9, "<"&3)>0,
       {
         QUERY(A2:C9,"SELECT * WHERE B=3 ORDER BY C ASC");
         QUERY(A2:C9,"SELECT * WHERE B<>3 ORDER BY C DESC")
       },
       QUERY(A2:C9,"SELECT * WHERE B=3 ORDER BY C DESC")),
     QUERY(A2:C9,"SELECT * WHERE B<>3 ORDER BY C DESC")),
   2,
   FALSE
 )

Screenshot

Explained

The SORT formula is fed a range of two combined data sets. The first uses a QUERY formula that filters for column B to be 3 and sorts ascendingly. The second data set also uses a QUERY formula to filter the same column B, but excluding 3 and sorting descendingly. Finally, the SORT formula sorts column B descendingly, leaving you with the desired result.

Example

I've created an example file for you: Is it possible to add an IF to a SORT function?

Answer 2

This can be done by creating an additional column, using it for sorting, and then disregarding it. Example:

=array_constrain(sort({A2:C10, if(B2:B10=3, C2:C10, -C2:C10)}, 2, true, 4, true), 1e7, 3)

Explanation:

• {A2:C10, if(B2:B10=3, C2:C10, -C2:C10)} creates a 4-column array where the content of the fourth column is the same as C if B is 3, and otherwise it's -C. (This is assuming C is numeric; times and dates are numeric behind the scenes)

• sort sorts by column 2 (B) ascending and then by column 4 (the additional one) ascending. The latter means sorting by C ascending when B is 3, and descending otherwise.

• array_constrain constrains the output to at most 10,000,000 rows and 3 columns; the point being that the 4th column is no longer needed, so it's not shown.

Answer 3

ok, recently named functions where allowed, so now it is very easy just go to Data / Named functions / Add new function and create a function like

=If( sort_fl, sort( mtrx ,1,1), mtrx)

where argument mtrx is your matrix/range

and argument sort_fl is the sort flag {true/false}

Of course you can send in the function multiple arguments to control the sort, but you get the idea ...

cheers!