I have data in A1:C10, and then more in A14:C20. So there are some subtotal rows in between. How can I get the top 10 across these ranges? I'm actually just trying to show the C column. I know I'm supposed to use the Array {}, but not sure how to do that exactly (gives me errors).

I'd also be happy to pull from just one range if I could somehow get top 12 EXCLUDING top 2 (because the top 2 would be the larger sum rows).

Here's what the sortn formula is that's not working:

=sortn({c1:C10,c14:c20},10,1,{c1:c10,c14:c20},False)

This formula works when I only have one range. But for one Department, I need to pull from two ranges.

I'd also tried query:

=query({a1:c10,a14:c20},"Select C Order by C Desc Limit 10") 

but that didn't work.

This is on a work spreadsheet, so I can't share it without doing some anonymizing.

CodePudding user response：

use:

=SORTN({C1:C10; C14:C20}, 10, 1, {C1:C10; C14:C20}, 0)

CodePudding user response：

In addition to player0 answer, the reason why SORTN was returning #REF! Error was the size of the input array. There are 2 issues in that formula:

1. SORTN input expects a “rectangular” range, meaning that the columns provided must have the same number of rows. Declaring the array as {RANGE1,RANGE2} will return two columns, c1:C10 have 10 rows and c14:c20 have 7 rows.
2. SORTN also expects that the sort column parameter to be a column index, a single row or a single column of values.

You can refer to the SORTN() function documentation here and documentation on the Array notation here