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

 

[hagia_sofia]

I am not sure what you would like to filter and whether you have 365 but generally speaking it could be something like (adjust you ranges etc.):

=TAKE(SORT(AB13:AB39,,-1),5)

 

[baseball17bucks]

I am not sure what you would like to filter and whether you have 365 but generally speaking it could be something like (adjust you ranges etc.):

=TAKE(SORT(AB13:AB39,,-1),5)

Thank you but I think my post explains what I'm trying to do. I want to add another filter condition to the array formula in cell AA13 that will only return the Top 5 Accounts based on the Sumif Labor Cost values which are shown for reference in Column AB.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[baseball17bucks]

I’m using Office 365.

 

[Fluff]

Thanks for that.
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(VSTACK(u,s),2,1),5))

 

[baseball17bucks]

Thanks for the attempt - for some reason the following term in your equation is evaluating to "0". Any idea why?

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

Thanks again!

 

[Fluff]

That should give exactly the same result as your formula, because that's what it is.

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[baseball17bucks]

That should give exactly the same result as your formula, because that's what it is.

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Is there any other way to send you draft data? I can't seem to get XL2BB to install on my machine. It's a work computer so maybe there's a policy preventing the install.

 

[Fluff]

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.