I have a few columns of data and I am trying to find the top 3 highest values in one column based on meeting one condition in the other column. I also want to ignore hidden rows in my data since I use filters. I am using the AGGREGATE function as follows but it will not ignore hidden rows when I filter the data. Any help would be greatly appreciated.
AGGREGATE(14,5,D3:D17*(F3:F17=B4),1)
=AGGREGATE(14,5,IF(F3:F17=B4,D3:D17),1) as array formula
Thanks but unfortunately still not ignoring hidden rows when I filter. I've attached a simple example.
Last edited by buttonman; 06-23-2017 at 03:56 PM.
Which excel version you using. Aggregate function applies to Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010
Instead of aggregate use Large function. I have done some changes in your sheet & using large function with top 3 base on criteria year.
Refer column "A" "B" "C"
Look attach file.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
Thanks AVK. I use Excel 2013. Unfortunately, your formula does not ignore hidden rows when I filter. For example, pull "4" out of stage column via filter and the answer in cell C1 does not change. Any of ideas? Thank you.
NOTE: The function will not ignore hidden rows, nested subtotals or nested aggregates if the array argument includes a calculation, for example: =AGGREGATE(14,3,A1:A100*(A1:A100>0),1)
https://support.office.com/en-us/art...6-E19993FA26DF
Thanks Tim. I looked at that a few times but clearly missed that note. Any other ideas on how I could accomplish this task?
=large(subtotal(9,offset(d2,row(a1:a15),))*(f3:f17=b1),1)
The Aggregate-function can't handle the marked part of the formula
Formula:
=AGGREGATE(14,5,D3:D17*(F3:F17=B4),1)
In the attached workbook I introduced a helper column which deals with that part of the formula.
Also introduced a cell where one of the years is selected. The helper column looks at the selection cell.
Changed your aggregate-formula to look at the helper column.
Alternatively - if you don't want to use the helper column and the selection cell - you could just leave out the marked part of the formula and manualy filter the year column.
Last edited by Tsjallie; 06-23-2017 at 05:17 PM.
Cheers!
Tsjallie
--------
If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!
If you think design is an expensive waste of time, try doing without ...
Finally getting back to this issue. Thank you Tim. That last formula worked beautifully!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks