Sort/Filter based on SUMIF Value

[baseball17bucks]

Hello - In my example below, I'm trying to use an array formula to filter the Top 5 "Accounts" in column AA based on the "Sumif Labor Cost" in column AB. I want to ultimately do this with a single array formula in cell AA13. I've provided the rank in column AC to show the top 5 items that I want to be left with.

Currently, I'm using this sort/unique/filter formula in column AA. You can see that I'm filtering this already based on the "CA Level" and "Resource Element of Cost" fields:

=SORT(UNIQUE(FILTER(tbl_Data[CA],(tbl_Data[CA Level]=3)*(tbl_Data[Resource.Element of Cost]="Labor"))))

My underlying data set has multiple line items for each Account, so I need to filter the Accounts based on the Total Labor Cost for all line items for that account.

The Sumif Labor Cost column has the following array formula for reference.

=SUMIFS(tbl_Data[Value],tbl_Data[Resource.Element of Cost],"Labor",tbl_Data[CA],AA13#)

What filter condition can I apply to the array formula for "Account" in cell AA13 so that my array only returns the Top 5 Accounts based on total Labor Cost?

Thanks for any help you can provide!

JF

 

[baseball17bucks]

You can copy/paste the data, or you can upload to a share site such as OneDrive/GoogleDrive. Then mark for sharing & post the link to the thread.

Loading Google Sheets

Hopefully this works!

 

[Fluff]

Thanks for that. A slight typo, it should be

=LET(u,UNIQUE(FILTER(tbl_Data[CA],(tbl_Data[CA Level]=3)*(tbl_Data[Resource.Element of Cost]="Labor"))),s,SUMIFS(tbl_Data[Value],tbl_Data[Resource.Element of Cost],"Labor",tbl_Data[CA],u),TAKE(SORT(HSTACK(u,s),2,-1),5))

 

[baseball17bucks]

Thanks for that. A slight typo, it should be

=LET(u,UNIQUE(FILTER(tbl_Data[CA],(tbl_Data[CA Level]=3)*(tbl_Data[Resource.Element of Cost]="Labor"))),s,SUMIFS(tbl_Data[Value],tbl_Data[Resource.Element of Cost],"Labor",tbl_Data[CA],u),TAKE(SORT(HSTACK(u,s),2,-1),5))

Genius! However, this result yields two columns (account and total cost). How do I just return the Account field? I just want the first column. Thanks again!!

 

[Fluff]

How about

=LET(u,UNIQUE(FILTER(tbl_Data[CA],(tbl_Data[CA Level]=3)*(tbl_Data[Resource.Element of Cost]="Labor"))),s,SUMIFS(tbl_Data[Value],tbl_Data[Resource.Element of Cost],"Labor",tbl_Data[CA],u),TAKE(SORT(HSTACK(u,s),2,-1),5,1))