If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
- Forums
- Question Forums
- Excel Questions
- Thread starterbaseball17bucks
- Start dateTuesday at 10:59 PM
- Tags
- large formulamultiple criteriarank formulasumif
B
baseball17bucks
New Member
- Joined
- Oct 8, 2014
- Messages
- 16
- Tuesday at 10:59 PM
- #1
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:
Excel Formula:
=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.
Excel Formula:
=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
Excel Facts
Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by dateSort by votes
H
hagia_sofia
Board Regular
- Joined
- May 10, 2024
- Messages
- 122
- Office Version
- 365
- Platform
- Windows
- Yesterday at 2:28 AM
- #2
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.):
Excel Formula:
=TAKE(SORT(AB13:AB39,,-1),5)
Upvote0
B
baseball17bucks
New Member
- Joined
- Oct 8, 2014
- Messages
- 16
- Yesterday at 8:10 AM
- #3
hagia_sofia said:
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.):
Excel Formula:
=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.
Upvote0
Fluff
MrExcel MVP, Moderator
- Joined
- Jun 12, 2014
- Messages
- 91,187
- Office Version
- 365
- Platform
- Windows
- Yesterday at 8:21 AM
- #4
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’)
Upvote0
B
baseball17bucks
New Member
- Joined
- Oct 8, 2014
- Messages
- 16
- Yesterday at 10:32 AM
- #5
I’m using Office 365.
Upvote0
Fluff
MrExcel MVP, Moderator
- Joined
- Jun 12, 2014
- Messages
- 91,187
- Office Version
- 365
- Platform
- Windows
- Yesterday at 10:40 AM
- #6
Thanks for that.
How about
Excel Formula:
=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))
Upvote0
B
baseball17bucks
New Member
- Joined
- Oct 8, 2014
- Messages
- 16
- Yesterday at 2:19 PM
- #7
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!
Upvote0
Fluff
MrExcel MVP, Moderator
- Joined
- Jun 12, 2014
- Messages
- 91,187
- Office Version
- 365
- Platform
- Windows
- Yesterday at 2:24 PM
- #8
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.
Upvote0
B
baseball17bucks
New Member
- Joined
- Oct 8, 2014
- Messages
- 16
- Yesterday at 2:46 PM
- #9
Fluff said:
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.
Upvote0
Fluff
MrExcel MVP, Moderator
- Joined
- Jun 12, 2014
- Messages
- 91,187
- Office Version
- 365
- Platform
- Windows
- Yesterday at 2:56 PM
- #10
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.
Upvote0
You must log in or register to reply here.
Similar threads
R
- Question
Sumifs or Sumproduct formula within a month with multiple criteria
- rrmando18
- Jun 5, 2024
- Excel Questions
- Replies
- 6
- Views
- 191
Jun 5, 2024
rrmando18
R
R
- Question
Formula to assign category based on multiple criteria and then picking highest value
- redsheep79
- May 23, 2024
- Excel Questions
- Replies
- 2
- Views
- 128
May 23, 2024
Fluff
H
- Question
Filter, sorted, long hstack() formula
- henryg
- Apr 19, 2024
- Excel Questions
- Replies
- 2
- Views
- 254
Apr 20, 2024
henryg
H
- Question
Is it possible to do a multiple Filter-sort?
- FrumpyJones
- Mar 28, 2024
- Excel Questions
- Replies
- 3
- Views
- 388
Mar 28, 2024
Kaper
J
- Question
Marking Top 10 in Criteria List
- Jezzzza
- Apr 16, 2024
- Excel Questions
- Replies
- 1
- Views
- 213
Apr 16, 2024
Jezzzza
J
Forum statistics
- Threads
- 1,217,903
- Messages
- 6,139,295
- Members
- 450,192
- Latest member
- gnissen
Share this page
- Forums
- Question Forums
- Excel Questions