2

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

KGB91
  • 630
  • 2
  • 6
  • 24

1 Answers1

1

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.

Glitch_Doctor
  • 2,994
  • 3
  • 16
  • 30
  • The solution gives me apple in J2-J4, no matter the country or the value. See my edit. – KGB91 Sep 14 '18 at 11:50
  • That sounds like it's not been applied as an array, are you sure it was Ctrl + Shift + Enter 'ed? it should have { } around it in the formula bar if so – Glitch_Doctor Sep 14 '18 at 11:53
  • @KGB91 And cell `K2` is also an array formula? once they are set you should be able to drag them down... cell `J2` relies on information from `K2` to work properly... So if we can get K2 working first that'll be the starting point – Glitch_Doctor Sep 14 '18 at 11:58
  • K2 is an array now, but it gives me the value 0. `=MAX(SUMIFS($F$2:$F$6;$A$2:$A$6;$I2;$D$2:$D$6;$D$2:$D$6))` – KGB91 Sep 14 '18 at 12:01
  • @KGB91 Okay, I think `SUMIFS()` is returning 0 for all results which leads a match on 0 for every row, returning Apple for cell `J2`, the cause of this is probably that the text is not an exact match of "Sweden" in column `A:A`. There may be some trailing white space characters. EDIT: can't place the `CLEAN(TRIM())` inside the formula, it breaks it... so we'll use a wildcard: `=MAX(SUMIFS($F$2:$F$6;$A$2:$A$6;$I2&"*";$D$2:$D$6;$D$2:$D$6))` – Glitch_Doctor Sep 14 '18 at 12:05
  • Excel wont accept that formula... I have manually trimmed the data to be exact, so that should not be the problem. – KGB91 Sep 14 '18 at 12:08
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/180052/discussion-between-glitch-doctor-and-kgb91). – Glitch_Doctor Sep 14 '18 at 12:09
  • Ahh, the problems seems to be that I made the data file by copying the values from this post here which made the cells differ! It seems to work now. One last question: How do I do if I want a criteria for year too? That is, if I want to be able to get the largest `product` and its `sale` for a specific `year`? I both want the total for all years and for different years. – KGB91 Sep 14 '18 at 12:11