google-sheetsgoogle-sheets-formula

How to use a filter inside SortN

I'm trying to set up a sortn function with a filter inside, but for some reason, this function isn't working:

=sortn(filter(AssessScores,AssessScoreStudent=C4,AssessOppor=A$1),1,0,TimestampScores,false)

I get an error:

Error SORTN has mismatched range sizes. Expected row count: 2. column count: 1. Actual row count: 300, column count: 1.

This works:

=sortn(filter(AssessScores,AssessScoreStudent=C4,AssessOppor=A$1),1,0)

So something is going wrong when the sort column is added.

All the named ranges are on the same workbook and sheet, the function is running in another sheet of the workbook.

How do I fix this?

Solution

  • If you give sort() or sortn() an array as the sort column, the array must be as tall as the data to be sorted. Use something like this:

    =sortn( 
  filter(AssessScores, AssessScoreStudent = C4, AssessOppor = A$1), 
  1, 0, 
  filter(TimestampScores, AssessScoreStudent = C4, AssessOppor = A$1), 
  false 
)