cut off data at item #20

[btardo01]

I am using the following formula, which is returning a #spill error. The formula is supposed to be taking the top 20 items; however, the problem is that item 20 and item 21 are the exact same value. I need to keep my total row were it is so I need to somehow just stop at item 20 even it the value is the same as item 21. Can I somehow amend the formula so that if any items past item 20 has the same value as item 20, it just picks the first one it get to?

=SORT(FILTER(FILTER(StaffData!B:Y,StaffData!M:M>=LARGE(StaffData!M:M,MIN(20,COUNTIF(StaffData!M:M,">0")))),{1,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0}),2,-1)

 

[StephenCrump]

I assume you're not running Excel 2016 any more?

Is this what you need?

=TAKE(SORT(CHOOSECOLS(FILTER(StaffData!B:M,StaffData!M:M>0),1,12),2,-1),20)

 

[btardo01]

yes, that seems to have worked. Thank you very much.