Why is this combined SORTYBY,FILTER,SEQUENCE,INDEX,COUNTIF formula sorting partially?

Question

I have formula calculated values in columns H:O (The number of rows adjust dynamically based on how many filtered values are in columns A:G. The headers end on Row 2)

The calculations in columns H:O tell whether or not the status of something is due, upcoming, or compliant based on hours. And other relevant data. Column O ranks these values with a number on what is most overdue.

These work perfectly.

Below is a filtered array that only displays Due items and it supposed to rank them in Ascending order from 1 to the lowest number (This rank is listed in value O). But it only partially sorts this, like 3,6,9,11,2. etc.

=SORTBY(FILTER(INDEX($A:$O,SEQUENCE(ROWS($A:$O)),{6,7,3,11,15}),$N:$N="DUE"),INDEX($O:$O,SEQUENCE(COUNTIF($N:$N,"DUE"))),1)

I did try pasting as values and doing a simplier formula since it was only dealing with values and not a filtered range and it sorted correctly. I tried adjusting the values in Column O to be General, number, text, etc, and it still sorted wonky.

Answer 1

INDEX($O:$O,SEQUENCE(COUNTIF($N:$N,"DUE"))) is not returning the same rows as FILTER(INDEX($A:$O,SEQUENCE(ROWS($A:$O)),{6,7,3,11,15}),$N:$N="DUE")

It is returning the top cells in column O totaling the number of cells in the whole N column that = DUE. IE if there are 10 cell that = DUE then it is returning O1:O10 not the ones that are joining the DUE:

Instead use SORT and just dictate the 5th column of your return:

=SORT(FILTER(INDEX($A:$O,SEQUENCE(ROWS($A:$O)),{6,7,3,11,15}),$N:$N="DUE"),5,1)

if you really want to use SORTBY then use a second FILTER:

=SORTBY(FILTER(INDEX($A:$O,SEQUENCE(ROWS($A:$O)),{6,7,3,11,15}),$N:$N="DUE"),FILTER($O:$O,$N:$N="DUE"),1)

But that is doing extra unneeded calculations.