One way:
=MAX(IF(A$2:A$8=A2,B$2:B$8))
confirmed with Ctrl+Shift+Enter.
Awesome. That works. THANKS! On to another complication:
The keys in column A are text values, in this format:
<table x:str="" style="border-collapse: collapse; width: 129pt;" border="0" cellpadding="0" cellspacing="0" width="172"><tbody><tr style="height: 12.75pt;" height="17"></tr><tr style="height: 20.25pt;" height="27"><td class="xl25" style="height: 20.25pt; width: 48pt;" x:num="" width="64" height="27">1</td><td class="xl26" style="width: 81pt;" x:err="#VALUE!" align="center" width="108">#VALUE!
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl27" style="height: 12.75pt;" height="17">1.1</td><td class="xl24" x:err="#VALUE!" align="center">#VALUE!</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">1.1.1</td><td class="xl23" x:num="40360" align="right">7/1/2010</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">1.1.2</td><td class="xl23" x:num="40391" align="right">8/1/2010</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl27" style="height: 12.75pt;" height="17">1.2</td><td class="xl24" x:err="#VALUE!" align="center">#VALUE!</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">1.2.1</td><td class="xl23" x:num="40452" align="right">10/1/2010</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">1.2.2</td><td class="xl23" x:num="40483" align="right">11/1/2010</td></tr></tbody></table>
The idea is that the '1' header row rolls up all the 1.*.* rows. The '1.1' header row only rolls up the 1.1.* rows. The '1' row is easy and i have that working with =MAX(IF(LEFT(A2:A1000,1)=LEFT(A1,1),B2:B1000)) But I'm struggling with coming up with a working formula for the '1.1' roll up of '1.1.*' I need to use some string functions that will break apart the keys in column a as they can be >2 characters sometimes (like 1.10.10) so I cant just use a straight left,3 function to return it like like I do for the '1' roll up.
This is what I've got so far for the '1.1' rollup:
=MAX(IF(LEFT(A3:A1000,FIND(".",A3:A1000,3)-1)=A2,B3:B1000)) and I'm getting a VALUE error.
Basically i'm looking to return the max of rows below the 1.1 row where their key is 1.1.*. My string function =LEFT(A3,FIND(".",A3,3)-1) appears to work by itself and correctly return only the 1.1 value but when i plug it into the array it returns VALUE Any ideas?
<table x:str="" style="border-collapse: collapse; width: 129pt;" border="0" cellpadding="0" cellspacing="0" width="172"><col style="width: 48pt;" width="64"> <col style="width: 81pt;" width="108"> <tbody><tr style="height: 20.25pt;" height="27"> <td class="xl25" style="height: 20.25pt; width: 48pt;" x:num="" width="64" height="27">
</td> <td class="xl26" style="width: 81pt;" x:err="#VALUE!" align="center" width="108">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl24" x:err="#VALUE!" align="center">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl23" x:num="40360" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td><td class="xl23" x:num="40391" align="right">
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl27" style="height: 12.75pt;" height="17">
</td><td class="xl24" x:err="#VALUE!" align="center">
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">
</td><td class="xl23" x:num="40452" align="right">
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">
</td><td class="xl23" x:num="40483" align="right">
</td></tr></tbody></table>