MAXIF function... Does it exist?

[Chuck B. Right]

Hello Group-

Is there a way to return the MAX number in column C for each account rep in column B (similar to the SUMIF function)? I would prefer not to use a pivot table to get the result. However, if a pivot table is the best solution for this problem, then I am not opposed to using it.

Advanced thanks,
Chuck B. Right

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">16</td><td style="font-weight: bold;border-bottom: 1px solid black;;">As of Date</td><td style="font-weight: bold;border-bottom: 1px solid black;;">Account Rep</td><td style="font-weight: bold;border-bottom: 1px solid black;;">RNWD</td><td style="font-weight: bold;border-bottom: 1px solid black;;">CXLD</td><td style="font-weight: bold;border-bottom: 1px solid black;;">% CXLD</td><td style="font-weight: bold;border-bottom: 1px solid black;;">% RNWD</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;border-top: 1px solid black;;">5/5/2011</td><td style="border-top: 1px solid black;;">Sneezy</td><td style="text-align: right;border-top: 1px solid black;;">48</td><td style="text-align: right;border-top: 1px solid black;;">6</td><td style="text-align: right;border-top: 1px solid black;;">4.08%</td><td style="text-align: right;border-top: 1px solid black;;">32.65%</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">5/5/2011</td><td style=";">Sleepy</td><td style="text-align: right;;">47</td><td style="text-align: right;;">5</td><td style="text-align: right;;">0.34%</td><td style="text-align: right;;">3.18%</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">5/5/2011</td><td style=";">Dopey</td><td style="text-align: right;;">53</td><td style="text-align: right;;">11</td><td style="text-align: right;;">0.44%</td><td style="text-align: right;;">2.12%</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">5/5/2011</td><td style=";">Doc</td><td style="text-align: right;;">52</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0.76%</td><td style="text-align: right;;">3.96%</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">5/5/2011</td><td style=";">Happy</td><td style="text-align: right;;">50</td><td style="text-align: right;;">8</td><td style="text-align: right;;">0.33%</td><td style="text-align: right;;">2.06%</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">5/5/2011</td><td style=";">Bashful</td><td style="text-align: right;;">49</td><td style="text-align: right;;">7</td><td style="text-align: right;;">0.30%</td><td style="text-align: right;;">2.08%</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">5/5/2011</td><td style=";">Grumpy</td><td style="text-align: right;;">51</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0.65%</td><td style="text-align: right;;">3.67%</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">5/25/2011</td><td style=";">Sneezy</td><td style="text-align: right;;">299</td><td style="text-align: right;;">8</td><td style="text-align: right;;">5.44%</td><td style="text-align: right;;">203.40%</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">5/25/2011</td><td style=";">Sleepy</td><td style="text-align: right;;">298</td><td style="text-align: right;;">7</td><td style="text-align: right;;">0.47%</td><td style="text-align: right;;">20.14%</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">5/25/2011</td><td style=";">Dopey</td><td style="text-align: right;;">304</td><td style="text-align: right;;">13</td><td style="text-align: right;;">0.52%</td><td style="text-align: right;;">12.18%</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">5/25/2011</td><td style=";">Doc</td><td style="text-align: right;;">303</td><td style="text-align: right;;">12</td><td style="text-align: right;;">0.91%</td><td style="text-align: right;;">23.09%</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">5/25/2011</td><td style=";">Happy</td><td style="text-align: right;;">301</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0.41%</td><td style="text-align: right;;">12.39%</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">5/25/2011</td><td style=";">Bashful</td><td style="text-align: right;;">300</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0.38%</td><td style="text-align: right;;">12.72%</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">5/25/2011</td><td style=";">Grumpy</td><td style="text-align: right;;">53</td><td style="text-align: right;;">11</td><td style="text-align: right;;">0.79%</td><td style="text-align: right;;">3.82%</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">6/19/2011</td><td style=";">Sneezy</td><td style="text-align: right;;">704</td><td style="text-align: right;;">11</td><td style="text-align: right;;">7.48%</td><td style="text-align: right;;">478.91%</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">6/19/2011</td><td style=";">Sleepy</td><td style="text-align: right;;">703</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0.68%</td><td style="text-align: right;;">47.50%</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;">6/19/2011</td><td style=";">Dopey</td><td style="text-align: right;;">709</td><td style="text-align: right;;">16</td><td style="text-align: right;;">0.64%</td><td style="text-align: right;;">28.42%</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;;">6/19/2011</td><td style=";">Doc</td><td style="text-align: right;;">708</td><td style="text-align: right;;">15</td><td style="text-align: right;;">1.14%</td><td style="text-align: right;;">53.96%</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;;">6/19/2011</td><td style=";">Happy</td><td style="text-align: right;;">706</td><td style="text-align: right;;">13</td><td style="text-align: right;;">0.54%</td><td style="text-align: right;;">29.07%</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;;">6/19/2011</td><td style=";">Bashful</td><td style="text-align: right;;">705</td><td style="text-align: right;;">12</td><td style="text-align: right;;">0.51%</td><td style="text-align: right;;">29.89%</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;;">6/19/2011</td><td style=";">Grumpy</td><td style="text-align: right;;">60</td><td style="text-align: right;;">14</td><td style="text-align: right;;">1.01%</td><td style="text-align: right;;">4.32%</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: right;;">7/10/2011</td><td style=";">Sneezy</td><td style="text-align: right;;">1265</td><td style="text-align: right;;">16</td><td style="text-align: right;;">10.88%</td><td style="text-align: right;;">860.54%</td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: right;;">7/10/2011</td><td style=";">Sleepy</td><td style="text-align: right;;">1264</td><td style="text-align: right;;">15</td><td style="text-align: right;;">1.01%</td><td style="text-align: right;;">85.41%</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: right;;">7/10/2011</td><td style=";">Dopey</td><td style="text-align: right;;">1270</td><td style="text-align: right;;">21</td><td style="text-align: right;;">0.84%</td><td style="text-align: right;;">50.90%</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="text-align: right;;">7/10/2011</td><td style=";">Doc</td><td style="text-align: right;;">1101</td><td style="text-align: right;;">20</td><td style="text-align: right;;">1.52%</td><td style="text-align: right;;">83.92%</td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: right;;">7/10/2011</td><td style=";">Happy</td><td style="text-align: right;;">1267</td><td style="text-align: right;;">18</td><td style="text-align: right;;">0.74%</td><td style="text-align: right;;">52.16%</td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="text-align: right;;">7/10/2011</td><td style=";">Bashful</td><td style="text-align: right;;">1266</td><td style="text-align: right;;">17</td><td style="text-align: right;;">0.72%</td><td style="text-align: right;;">53.67%</td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: right;;">7/10/2011</td><td style=";">Grumpy</td><td style="text-align: right;;">75</td><td style="text-align: right;;">19</td><td style="text-align: right;;">1.37%</td><td style="text-align: right;;">5.40%</td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style="text-align: right;;">7/16/2011</td><td style=";">Sneezy</td><td style="text-align: right;;">1266</td><td style="text-align: right;;">17</td><td style="text-align: right;;">11.56%</td><td style="text-align: right;;">861.22%</td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style="text-align: right;;">7/16/2011</td><td style=";">Sleepy</td><td style="text-align: right;;">1265</td><td style="text-align: right;;">16</td><td style="text-align: right;;">1.08%</td><td style="text-align: right;;">85.47%</td></tr><tr ><td style="color: #161120;text-align: center;">47</td><td style="text-align: right;;">7/16/2011</td><td style=";">Dopey</td><td style="text-align: right;;">1272</td><td style="text-align: right;;">21</td><td style="text-align: right;;">0.84%</td><td style="text-align: right;;">50.98%</td></tr><tr ><td style="color: #161120;text-align: center;">48</td><td style="text-align: right;;">7/16/2011</td><td style=";">Doc</td><td style="text-align: right;;">1103</td><td style="text-align: right;;">20</td><td style="text-align: right;;">1.52%</td><td style="text-align: right;;">84.07%</td></tr><tr ><td style="color: #161120;text-align: center;">49</td><td style="text-align: right;;">7/16/2011</td><td style=";">Happy</td><td style="text-align: right;;">1268</td><td style="text-align: right;;">19</td><td style="text-align: right;;">0.78%</td><td style="text-align: right;;">52.20%</td></tr><tr ><td style="color: #161120;text-align: center;">50</td><td style="text-align: right;;">7/16/2011</td><td style=";">Bashful</td><td style="text-align: right;;">1267</td><td style="text-align: right;;">18</td><td style="text-align: right;;">0.76%</td><td style="text-align: right;;">53.71%</td></tr><tr ><td style="color: #161120;text-align: center;">51</td><td style="text-align: right;;">7/16/2011</td><td style=";">Grumpy</td><td style="text-align: right;;">76</td><td style="text-align: right;;">19</td><td style="text-align: right;;">1.37%</td><td style="text-align: right;;">5.48%</td></tr><tr ><td style="color: #161120;text-align: center;">52</td><td style="text-align: right;;">7/31/2011</td><td style=";">Sneezy</td><td style="text-align: right;;">1280</td><td style="text-align: right;;">16</td><td style="text-align: right;;">10.88%</td><td style="text-align: right;;">870.75%</td></tr><tr ><td style="color: #161120;text-align: center;">53</td><td style="text-align: right;;">7/31/2011</td><td style=";">Sleepy</td><td style="text-align: right;;">2259</td><td style="text-align: right;;">15</td><td style="text-align: right;;">1.01%</td><td style="text-align: right;;">152.64%</td></tr><tr ><td style="color: #161120;text-align: center;">54</td><td style="text-align: right;;">7/31/2011</td><td style=";">Dopey</td><td style="text-align: right;;">2265</td><td style="text-align: right;;">21</td><td style="text-align: right;;">0.84%</td><td style="text-align: right;;">90.78%</td></tr><tr ><td style="color: #161120;text-align: center;">55</td><td style="text-align: right;;">7/31/2011</td><td style=";">Doc</td><td style="text-align: right;;">1269</td><td style="text-align: right;;">20</td><td style="text-align: right;;">1.52%</td><td style="text-align: right;;">96.72%</td></tr><tr ><td style="color: #161120;text-align: center;">56</td><td style="text-align: right;;">7/31/2011</td><td style=";">Happy</td><td style="text-align: right;;">2262</td><td style="text-align: right;;">18</td><td style="text-align: right;;">0.74%</td><td style="text-align: right;;">93.12%</td></tr><tr ><td style="color: #161120;text-align: center;">57</td><td style="text-align: right;;">7/31/2011</td><td style=";">Bashful</td><td style="text-align: right;;">1276</td><td style="text-align: right;;">17</td><td style="text-align: right;;">0.72%</td><td style="text-align: right;;">54.09%</td></tr><tr ><td style="color: #161120;text-align: center;">58</td><td style="text-align: right;;">7/31/2011</td><td style=";">Grumpy</td><td style="text-align: right;;">130</td><td style="text-align: right;;">19</td><td style="text-align: right;;">1.37%</td><td style="text-align: right;;">9.37%</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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">NewYear</p><br /><br />

 

[VBACO]

Pretty sure you will have to write a custom function for that.

 

[Robert Mika]

Hi chuck and welcome to the board:

Try this:

=MAX(IF($B$17:$B$58=B17,$C$17:$C$58))

Array formula
Confirm CTRL+SHIFT+ENTER
copy down.


<table width="128" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" width="64" height="20">Sneezy</td> <td style="width:48pt" width="64" align="right">1280</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Sleepy</td> <td align="right">2259</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Dopey</td> <td align="right">2265</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Doc</td> <td align="right">1269</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Happy</td> <td align="right">2262</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Bashful</td> <td align="right">1276</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Grumpy</td> <td align="right">130</td> </tr> </tbody></table>

 

[Aladin Akyurek]

G17, control+shift+enter, not just enter, and copy down:

=MAX(IF($B$17:$B$58=B17,$C$17:$C$58))

A pivot table would be appropriate too...

<TABLE style="WIDTH: 137pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=182><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3214" width=90><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3271" width=92><TBODY><TR style="HEIGHT: 25.8pt" height=34><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 68pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 25.8pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl63 height=34 width=90>Row Labels</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 69pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl63 width=92>Max of RNWD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19>Bashful</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1276</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19>Doc</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1269</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19>Dopey</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2265</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19>Grumpy</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>130</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19>Happy</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2262</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19>Sleepy</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2259</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19>Sneezy</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1280</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl64 height=19>Grand Total</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl63 align=right>2265</TD></TR></TBODY></TABLE>

 

[VBACO]

If this is something you have to do frequently then I still like the custom function option.

Function MaxIF(MRange, Critera, VRange)
'MRange is the range of cells that you are looking for your match
'Critera is what you are looking for
'VRANGE is the range of cells that contain the numeric values
 
Max = -1000000
    For i = 1 To MRange.Count
        If (MRange.Cells(i).Value = Critera) Then
            If Mas < VRange.Cells(i).Value Then
                Max = VRange.Cells(i).Value
            End If
        End If
    Next i
MaxIF = Max
End Function

 

[Chuck B. Right]

Thanks for your help. I modified your code to apply it to a report that I do run frequently.

 

[Chuck B. Right]

Thanks Robert! I have trouble knowing when to use an array function.

 

[Robert Mika]

Hi Chuck,
A good page about Array formuals:

http://www.cpearson.com/excel/ArrayFormulas.aspx