Sumproduct / ranked values

[Nathalie Sicard]

Hello everybody

I need to do this and can't find a way.

I have a table with these columns:
A: Zone
B: Territory
C: Product
D: Store format
E: Store name
F: Sales (some data is non numeric)

So what I need to do is a sheet in which I have 2 drop down values: product and storeformat.
Given those drop down values (which might change) I need to display the name of the top 2 territories with highest sales. Therefore I should have 4 cells:
- Territory top 1 sales
- Sales of territory top 1 sales
- Territory top 2 sales
- Sales of territory top 2 sales

I've been working with the formula sumproduct to bring the data, but can't find a way.

Thank you very much!

Nathalie

 

[samfolds]

Bonjour Nathalie,

From my point of view, it is necessary to create a table with the total sales of all territories. If you do not want to show it, you can hide it.

Create the table by entering all territories and then using SUMPRODUCT. I suggest making that table to the right of your data (Column H and I). Put your drop down list further right (say column K2 for product and K3 for store format) :

After entering the territory in H, enter and copy down in I (starting with row 2):

=SOMMEPROD(($B$2:$B$1000=H2)*($C$2:$C$1000=$K$2)*($D$2:$D$1000=$K$3)*($F$2:$F$1000))

Then, we want the 1st and 2nd territories.

In the cell you wish to see the name of the 1st territory :

=INDEX($H$2:$I$11,MATCH(MAX($I$2:$I$11),$I$2:$I$11,0),1)

In the cell on the right (1st territory cell) :

=SUMPRODUCT(($B$2:$A$1000=INDEX($H$2:$I$11,MATCH(MAX($I$2:$I$11),$I$2:$I$11,0),1))*B2:B11)

In the cell you wish to see the name of the 2nd territory :

=INDEX($H$2:$I$11,MATCH(LARGE($I$2:$I$11,2),$I$2:$I$11,0),1)

In the cell on the right (2nd territory cell) :

=SUMPRODUCT(($B$2:$B$1000=INDEX($H$2:$I$11,MATCH(LARGE($I$2:$I$11,2),$I$2:$I$11,0),1))*B2:B11)


J'espere que ça t'aidera.

Samfolds

 

[Nathalie Sicard]

Thank you very much, I'll try it as soon as I solve some other challenge, I actually don't need to sum the sales, but to display the highest value of sales (like a Max) but the formula ain't working.

 

[samfolds]

Ok so I found out an easier and faster way to do what you need :

Use these array formula (Confirmed with Control+Alt+Enter)

For the max :
=MAX(IF(C2:C1000=K2,IF(D2:D1000=K3,F2:F1000)))

For the second max :
=LARGE(IF(C2:C1000=K2,IF(D2:D1000=K3,F2:F1000)),2)

Hope this helps!

Samfolds

 

[mgirvin]

Taking up on samfolds's good advice, if the formula for finding the first and second largest values were to go into cells A1 and A2, you could use this one formula:

=LARGE(IF($C$2:$C$1000=$K$2,IF($D$2:$D$1000=$K$3,$F$2:$F$1000)),ROWS(A$1:A1))

When copy enter the formula with Ctrl + Shift + Enter (not just Enter) and copy it down it will show the first and second largest values.


If it were to go in some other cell, say M2, the formula would look like this:

=LARGE(IF($C$2:$C$1000=$K$2,IF($D$2:$D$1000=$K$3,$F$2:$F$1000)),ROWS(M$1:M1))


If you had Excel 2010, you could use this formula that would not require Ctrl + Shift += Enter:

=AGGREGATE(14,6,($F$2:$F$1000)/(($C$2:$C$1000=$K$2)*($D$2:$D$1000=$K$3)),ROWS(A$3:A3))

 

[Nathalie Sicard]

Thank you very much everyone for your input. I actually had to deliver that report on wednesday and used the first LARGE formula listed, but I'll try this out just to check if it works better.

Thank you for your time! I wouldn't make it without this forum