Sumproduct / ranked values

Nathalie Sicard

New Member
Joined
May 23, 2010
Messages
39
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

samfolds

Board Regular
Joined
Jul 2, 2009
Messages
191
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
 
Last edited:

Nathalie Sicard

New Member
Joined
May 23, 2010
Messages
39
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

Board Regular
Joined
Jul 2, 2009
Messages
191
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

Well-known Member
Joined
Dec 15, 2005
Messages
1,216
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

New Member
Joined
May 23, 2010
Messages
39
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,133,157
Messages
5,657,159
Members
418,363
Latest member
Debating_Earth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top