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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

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,213
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 :)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,996
Messages
5,508,641
Members
408,689
Latest member
SamSan78

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top