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

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?

• Google Sheets allows for scripting in sheets. Maybe you should look into that as an option.
– user144900
Feb 23, 2017 at 5:59

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
)
``````

## 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?

• At first glance, this looks like exactly what I'm looking for. However, when I change it slightly to be used for a separate data set (this one set from E3:G6), when I change everything and end up with `=SORT({QUERY(Rounds!E3:G6,"SELECT * WHERE F=3 ORDER BY G ASC");QUERY(Rounds!E3:G6,"SELECT * WHERE F<>3 ORDER BY G DESC")},2, FALSE)` (the data it's sorting is actually in sheet `Rounds` obviously) and it returns "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows. Mar 4, 2017 at 15:22
• Actually, would it do this if one of the query parameters wasn't met at all? In this instance, I think it's doing it because in set 2 it doesn't have any with 3? Mar 4, 2017 at 16:09
• Is there a way to make it so that it wouldn't do that? Mar 4, 2017 at 20:40

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.

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!