Find second largest number in column

rileybloke

New Member
Joined
Feb 7, 2013
Messages
43
Hi, is there a way to find the second largest number in a column of data?
I need to return the second largest ORD_QTY for only Q quantities, and for each item.
I have tried RANKX but cant seem to get it to work, or is there an easier method?
Thanks all in advance :)


ITEM Date PQX ORD_QTY
121209-0001 01/10/2014 00:00 Q 560
121209-0001 02/10/2014 00:00 Q 560
121209-0001 06/10/2014 00:00 Q 1680
121209-0001 13/10/2014 00:00 Q 2760
121209-0001 20/10/2014 00:00 Q 2800
121209-0001 27/10/2014 00:00 Q 1400
121209-0001 03/11/2014 00:00 Q 2640
121209-0001 10/11/2014 00:00 Q 2640
121209-0001 17/11/2014 00:00 Q 2600
121209-0001 24/11/2014 00:00 Q 2640
121209-0001 01/12/2014 00:00 Q 2640
121209-0001 08/12/2014 00:00 Q 2640
15B533-0002 01/10/2014 00:00 Q 72
15B533-0002 06/10/2014 00:00 Q 144
15B533-0002 13/10/2014 00:00 Q 432
15B533-0002 20/10/2014 00:00 Q 360
15B533-0002 27/10/2014 00:00 Q 144
15B533-0002 03/11/2014 00:00 Q 288
15B533-0002 10/11/2014 00:00 Q 288
15B533-0002 17/11/2014 00:00 Q 288
15B533-0002 24/11/2014 00:00 Q 288
15B533-0002 01/12/2014 00:00 Q 288
15B533-0002 08/12/2014 00:00 Q 360
15B533-0002 15/12/2014 00:00 Q 288
15B533-0002 22/12/2014 00:00 Q 288
15B533-0002 29/12/2014 00:00 Q 288
15B533-0002 05/01/2015 00:00 Q 288
15B533-0002 12/01/2015 00:00 Q 288
15B533-0002 19/01/2015 00:00 Q 288
15B533-0002 26/01/2015 00:00 Q 288
15B533-0002 02/02/2015 00:00 Q 288
15B533-0002 09/02/2015 00:00 Q 288
15B533-0002 16/02/2015 00:00 Q 288
15B533-0002 23/12/2014 00:00 X 1224
15B533-0002 20/01/2015 00:00 X 612
121209-0001 22/09/2014 00:00 P 1280
121209-0001 29/09/2014 00:00 P 2720
121209-0001 06/10/2014 00:00 P 2400
121209-0001 13/10/2014 00:00 P 1920
121209-0001 20/10/2014 00:00 P 2000
121209-0001 27/10/2014 00:00 P 1600
121209-0001 03/11/2014 00:00 P 2720
121209-0001 10/11/2014 00:00 P 2560
121209-0001 17/11/2014 00:00 P 2960
15B533-0002 29/09/2014 00:00 P 765
15B533-0002 27/10/2014 00:00 P 306
15B533-0002 24/11/2014 00:00 P 1071
 

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
Try this:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><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><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">ITEM</td><td style="text-align: center;;">Date</td><td style="text-align: center;;"></td><td style="text-align: center;;">PQX</td><td style="text-align: center;;">ORD_QTY</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">1/10/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">560</td><td style="text-align: right;;"></td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">Q</td><td style="text-align: right;;">2760</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">2/10/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">560</td><td style="text-align: right;;"></td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">Q</td><td style="text-align: right;;">360</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">6/10/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">1680</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">13/10/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">2760</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">20/10/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">2800</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">27/10/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">1400</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">3/11/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">2640</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">10/11/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">2640</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">17/11/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">2600</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">24/11/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">2640</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">1/12/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">2640</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">8/12/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">2640</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">1/10/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">72</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">6/10/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">144</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">13/10/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">432</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">20/10/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">360</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">27/10/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">144</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">3/11/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">288</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">10/11/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">288</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">17/11/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">288</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">24/11/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">288</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">1/12/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">288</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">8/12/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">360</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">15/12/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">288</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">22/12/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">288</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">29/12/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">288</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">5/1/2015</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">288</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">12/1/2015</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">288</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">19/01/2015</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">288</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">26/01/2015</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">288</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">2/2/2015</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">288</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">9/2/2015</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">288</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">16/02/2015</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">Q</td><td style="text-align: center;;">288</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">23/12/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">X</td><td style="text-align: center;;">1224</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">20/01/2015</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">X</td><td style="text-align: center;;">612</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">22/09/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">P</td><td style="text-align: center;;">1280</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">29/09/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">P</td><td style="text-align: center;;">2720</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">6/10/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">P</td><td style="text-align: center;;">2400</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">13/10/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">P</td><td style="text-align: center;;">1920</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">20/10/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">P</td><td style="text-align: center;;">2000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">27/10/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">P</td><td style="text-align: center;;">1600</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">3/11/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">P</td><td style="text-align: center;;">2720</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">10/11/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">P</td><td style="text-align: center;;">2560</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style="text-align: center;;">121209-0001</td><td style="text-align: center;;">17/11/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">P</td><td style="text-align: center;;">2960</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">29/09/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">P</td><td style="text-align: center;;">765</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">47</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">27/10/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">P</td><td style="text-align: center;;">306</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">48</td><td style="text-align: center;;">15B533-0002</td><td style="text-align: center;;">24/11/2014</td><td style="text-align: center;;">0:00</td><td style="text-align: center;;">P</td><td style="text-align: center;;">1071</td><td style="text-align: right;;"></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.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet42</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><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: #DAE7F5;color: #161120">I2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">LARGE(<font color="Red">(<font color="Green">$A$2:$A$48=$G2</font>)*(<font color="Green">$D$2:$D$48=$H2</font>)*(<font color="Green">$E$2:$E$48</font>),2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">LARGE(<font color="Red">(<font color="Green">$A$2:$A$48=$G3</font>)*(<font color="Green">$D$2:$D$48=$H3</font>)*(<font color="Green">$E$2:$E$48</font>),2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
Sorry about my post. I have a habit of perusing zero reply posts and not noticing the forum they originated in. I just assume everything is strictly Excel related. Good Luck.
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
PrincessBride: Is the only way I can be satisfied. If I use my right, over too quickly. :)
 

Tianbas

Board Regular
Joined
Apr 29, 2014
Messages
101
Tianbas,Why don't you upgrade?
Our IT has tested PowerPivot V2 and it would work with our Excel and BI but not with Sharepoint. So we decided to live with less DAX formula but can at least use the autorefresh and reporting of Sharepoint.
We have our Sharepoint Enterprise on SQL Server 2008 and our IT will not /can not upgrade this in near future.
 

rileybloke

New Member
Joined
Feb 7, 2013
Messages
43
Our IT has tested PowerPivot V2 and it would work with our Excel and BI but not with Sharepoint. So we decided to live with less DAX formula but can at least use the autorefresh and reporting of Sharepoint.
We have our Sharepoint Enterprise on SQL Server 2008 and our IT will not /can not upgrade this in near future.
We use SharePoint 2010 to host some of our V2 projects. It did take the resources of several departments at Microsoft support to fix the background daily auto refresh, they have still yet to fix the user command refresh on the site.
 

MD610

Board Regular
Joined
Feb 7, 2012
Messages
188
What about something like this:
Code:
=
CALCULATE(
    MINX(
      TOPN(
             2,
             Data,
             Data[Ord_Qty]
      ),
      Data[Ord_Qty]
    ),
    Data[PQX] = "Q"
)
 

Forum statistics

Threads
1,078,520
Messages
5,340,931
Members
399,399
Latest member
SravanaSandhya

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