Filtering Top 20 by Value with Criteria

[bwheat76]

Since I am currently working on this project at work, I cannot post the data on this forum but I will try my best to explain the problem.

I am looking to pull the top 20 dollar amounts from a range, based on an additional criterion. For example, one field contains either of two possible variables: "Include" and "Exclude". I am looking to pull in the top 20 dollar amounts that are listed as "Include." I have tried using the { = LARGE(IF(... } method as referenced on other threads (large(if method source), however this returns an "N/A" error. I am thinking of using a MAXIFS formula to 1) return the next highest value from the value already calculated one row above, and 2) reference only those entries that contain an "include" variable.

This is what I have so far... = MAXIFS(dollar_amount, region, "include", dollar_amount, "<G7") ** where G7 is the cell above the cell I am calculating. This returns a value of 0 for all results.

Any idea how I can rework my formula or develop a new formula to draw the top dollar amounts using this criteria without using the LARGE(IF( method?

Thanks!!!

 

[Fluff]

Hi & welcome to MrExcel.

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’)

Your formula should be

= MAXIFS(dollar_amount, region, "include", dollar_amount, "<"&G7)

 

[bwheat76]

Wow, thank you for the rapid response. That worked perfectly. I'll update my account now, but I am using Version 2108.

I can see why you have earned the "MrExcel MVP" title!

 

[Fluff]

I am using Version 2108

That is a build rather than an xl version, I suspect that you are 2019, 2021 or 365