Cannot use SORTBY when cells hidden

[NicBKK]

Hello Gurus

I have some UNITS that have Revenue and those that are closed I hid the rows (needed for another report)
I want to have another field feeding off of that data to sort the Units by Revenue, from top to bottom
I am using the SORTBY but it's messed up because of the hidden rows I guess?

How can I do this better?

Thanks

[WideBoyDixon]

The SORTBY is working just fine - it's just that some data is now going into hidden rows. If you unhide those rows, you'll see all the data in the correct order. You have two choices I think:

1. Use the SORTBY in a range underneath your current table.
2. Use the SORTBY on another sheet.

WBD

[Glenn Kennedy]

However, if you wanted to sort ONLY the visible rows, use this on another sheet... or below your source data:

=LET(d,Sheet1!C7:D69,a,Sheet1!D7:D69,s,Sheet1!D7,v,SUBTOTAL(3,OFFSET(s,ROW(a)-ROW(s),)),va,FILTER(d,v),SORTBY(va,(INDEX(va,,2)),-1))

[Bo_Ry]

Please try

=LET(z,C7:D69,s,SEQUENCE(ROWS(z)),c,SUBTOTAL(102,OFFSET(z,s-1,1,1)),d,SUBTOTAL(102,OFFSET(z,,1,s)),INDEX(z,MOD(LARGE(INDEX(z,,2)*10^6+s,IF(c,d,s-d+SUM(c))),10^6),SEQUENCE(,2)))