Sum if the first 5 characters match

[sdchris]

Hello, I am working with two tables (both shown below). I want to be able to populate Column B in Table 2 by writing a lookup (I assume it's a variation of SUMIF) that will look at the value in Table 2, Column A, match it with the first five characters in Table 1, Column A, and sum the values in Table 1, Column B. Basically I'm looking to aggregate totals based on the first five characters. I hope this description is clear; please let me know if I need to clarify.

Table 1

[TABLE="width: 177"]
<tbody>[TR]
[TD]10.01.001[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]10.01.002[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]10.01.010[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]10.01.011[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]10.03.013[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]10.03.051[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]10.03.052[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Table 2

10.01
10.03

 

[Scott Huish]

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">10.01.001</td><td style="text-align:right; ">0</td><td > </td><td > </td><td style="text-align:right; ">10.01</td><td style="text-align:right; ">200</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">10.01.002</td><td style="text-align:right; ">100</td><td > </td><td > </td><td style="text-align:right; ">10.03</td><td style="text-align:right; ">300</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">10.01.010</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">10.01.011</td><td style="text-align:right; ">100</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">10.03.013</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">10.03.051</td><td style="text-align:right; ">300</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">10.10.011</td><td style="text-align:right; ">5</td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F1</td><td >=SUMIF(A:A,E1&"*",B:B)</td></tr><tr><td >F2</td><td >=SUMIF(A:A,E2&"*",B:B)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>