SUMPRODUCT with MIN and MAX?

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
Hi All,

I have a table on Sheet1 that looks like this:


Company --- User --- Count --- %
CompA --- UserA --- 100 --- 0
CompB --- UserB --- 50 --- 25
CompC --- UserA --- 75 --- 10
CompD --- UserC --- 50 --- 20
CompE --- UserB --- 25 --- 25
CompF --- UserC --- 50 --- 10
CompG --- UserB --- 10 --- 50



On Sheet2 I have this and need a formula to get the value from Sheet1:

User --- Company with lowest %
UserA --- [Need formula here]
UserB --- [Need formula here]
UserC --- [Need formula here]


The rule is find the Company that User works for with the lowest percantage. If there are multiple records with the lowest % then get the one with the highest count.

So the outcome should look like this:

User --- Company with lowest %
UserA --- CompA
UserB --- CompB (Company B and E has the lowest % but B has the highest count)
UserC --- CompF

I know SUMPRODUCT returns number so it might not work to get the company name.

PS: for this thing I'm working on Pivot tables wont help.

Any help is greatly appreciated.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,978
Office Version
2007
Platform
Windows
Try this array formula

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: center;color: #333333;background-color: #92D050;;">Company</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">User</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">Count</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="color: #333333;background-color: #BDD7EE;;">CompA</td><td style="background-color: #BDD7EE;;">UserA</td><td style="text-align: right;background-color: #BDD7EE;;">100</td><td style="text-align: right;background-color: #BDD7EE;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="color: #333333;background-color: #FFFF00;;">CompB</td><td style="background-color: #FFFF00;;">UserB</td><td style="text-align: right;background-color: #FFFF00;;">50</td><td style="text-align: right;background-color: #FFFF00;;">25</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="color: #333333;background-color: #BDD7EE;;">CompC</td><td style="background-color: #BDD7EE;;">UserA</td><td style="text-align: right;background-color: #BDD7EE;;">75</td><td style="text-align: right;background-color: #BDD7EE;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="color: #333333;;">CompD</td><td style=";">UserC</td><td style="text-align: right;;">50</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="color: #333333;background-color: #FFFF00;;">CompE</td><td style="background-color: #FFFF00;;">UserB</td><td style="text-align: right;background-color: #FFFF00;;">25</td><td style="text-align: right;background-color: #FFFF00;;">25</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="color: #333333;;">CompF</td><td style=";">UserC</td><td style="text-align: right;;">50</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="color: #333333;background-color: #FFFF00;;">CompG</td><td style="background-color: #FFFF00;;">UserB</td><td style="text-align: right;background-color: #FFFF00;;">100</td><td style="text-align: right;background-color: #FFFF00;;">50</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">User</td><td style=";">lowest %</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">UserA</td><td style=";">CompA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">UserB</td><td style=";">CompB</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">UserC</td><td style=";">CompF</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">{=INDEX(<font color="Blue">Sheet1!$A$1:$A$8,MAX(<font color="Red">(<font color="Green">Sheet1!$B$2:$B$8=A2</font>)*(<font color="Green">Sheet1!$C$2:$C$8=MAX(<font color="Purple">IF(<font color="Teal">Sheet1!$B$2:$B$8=A2,IF(<font color="#FF00FF">Sheet1!$D$2:$D$8=MIN(<font color="Navy">IF(<font color="Blue">Sheet1!$B$2:$B$8=A2,Sheet1!$D$2:$D$8</font>)</font>),Sheet1!$C$2:$C$8</font>)</font>)</font>)</font>)*(<font color="Green">Sheet1!$D$2:$D$8=MIN(<font color="Purple">IF(<font color="Teal">Sheet1!$B$2:$B$8=A2,Sheet1!$D$2:$D$8</font>)</font>)</font>)*(<font color="Green">ROW(<font color="Purple">Sheet1!$A$2:$A$8</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,811
This is a case where using a helper column can really simplify things. Consider:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Company</td><td style=";">User</td><td style=";">Count</td><td style=";">Percent</td><td style=";">Rank</td><td style="text-align: right;;"></td><td style=";">User</td><td style=";">Company w/ lowest %</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">CompA</td><td style=";">UserA</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">UserA</td><td style=";">CompA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">CompB</td><td style=";">UserB</td><td style="text-align: right;;">50</td><td style="text-align: right;;">25</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">UserB</td><td style=";">CompB</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">CompC</td><td style=";">UserA</td><td style="text-align: right;;">75</td><td style="text-align: right;;">10</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style=";">UserC</td><td style=";">CompF</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">CompD</td><td style=";">UserC</td><td style="text-align: right;;">50</td><td style="text-align: right;;">20</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">CompE</td><td style=";">UserB</td><td style="text-align: right;;">25</td><td style="text-align: right;;">25</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">CompF</td><td style=";">UserC</td><td style="text-align: right;;">50</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">CompG</td><td style=";">UserB</td><td style="text-align: right;;">10</td><td style="text-align: right;;">50</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">=INDEX(<font color="Blue">A:A,AGGREGATE(<font color="Red">15,6,ROW(<font color="Green">$A$2:$A$8</font>)/(<font color="Green">$B$2:$B$8=G2</font>)/(<font color="Green">$E$2:$E$8=1</font>),1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">B:B,B2,D:D,"<"&D2</font>)+COUNTIFS(<font color="Blue">B:B,B2,D:D,D2,C:C,">"&C2</font>)+1</td></tr></tbody></table></td></tr></table><br />
 

Forum statistics

Threads
1,078,541
Messages
5,341,061
Members
399,414
Latest member
EMW2159

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top