CODEWITHSUNDEEP
excel
KGB91
KGB91

Reputation: 679

Excel - find nth largest value based on criteria

This is a kind of an extension to this problem: Excel - Sum values from the data set based on criteria

I have a table like this:

Country Region    Code  Name of product Year   Value
Sweden  Stockholm 52    Apple           1995   1000
Sweden  Malmö     25    Pancake         1991   1500
Sweden  Malmö     52    Apple           1992   2470
Finland Helsinki  21    Candy           1987   2500
Denmark Copenhagen 52   Apple           1987   2571

What I want to do is to make a code that can give me the sum of the nth largest value of products that have been sold in a specific country.

That is, if I want to get the highest value for products sold in Sweden it should return Apple and the sum of sold apples, 3470.

edit: The solution of Glitch_Doctor:

enter image description here

Upvotes: 2

Views: 1096

Answers (1)

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

Firstly, for the value:

Both formulas are array formulas, please confirm the formula with Ctrl+Shift+Enter while still in the formula bar

=MAX(SUMIFS($F$2:$F$6,$A$2:$A$6,$I2,$D$2:$D$6,$D$2:$D$6))

This builds an array of SUMIFS() results for the country in cell $I2 and each product name then grabs the MAX() result.

And the product name:

=INDEX($D$2:$D$6,SMALL(IF(SUMIFS($F$2:$F$6,$A$2:$A$6,$I2,$D$2:$D$6,$D$2:$D$6)=$K2,ROW($D$2:$D$6)-1),1))

Now using the max SUMIFS() result, we reference the list of SUMIFS() results and get the row of the product (offset to the start of the INDEX()) and retrieve the smallest row number.

enter image description here

You can adjust MAX() in the first formula to be LARGE(,n) where n is the nth largest result.

Upvotes: 1

Related Questions