2

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?

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

3 Answers 3

2

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

enter image description here

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?

5
  • 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.
    – JarheadHME
    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?
    – JarheadHME
    Mar 4, 2017 at 16:09
  • @JarheadHME You're right about your last comment, but this wasn't included in your question. Mar 4, 2017 at 18:59
  • Is there a way to make it so that it wouldn't do that?
    – JarheadHME
    Mar 4, 2017 at 20:40
  • @JarheadHME Added second solution. Mar 5, 2017 at 13:47
0

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.

0
0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.