formula to summarize info in another table

[Christine365]

I'm using Excel 2003
In my example below I've added the column letters across and row numbers down.

I’m creating a spreadsheet template. Section 1 is populated manually with data (specifically, columns A, B, C, E, G, I, K, M). I would like Section 2, to populate itself. Looking at my example below, I would like a formula for cell B11 that looks at the info in Section 1 and adds all the cells in column H that relate to 2008. Cell B12 would do the same thing but for 2009 info. In my example there are 2 rows for 2008 and 3 rows for 2009 in Section 1; however another example may have 5 rows for 2008 and 2 rows for 2009, etc. as well as several more years. For cell B11 (as well as most of Section 2), instead of manually entering “=H3 + H4”, etc. and adjusting it every time I use this template, I would like it to recognize what cells to add together. I tried using a pivot table, which worked to summarize the data in Section 1 but I couldn't easily use the pivot table data to complete column H in Section 2. Thank you!</SPAN></SPAN>

A</SPAN>

B</SPAN>

C</SPAN>

D</SPAN>

E</SPAN>

F</SPAN>

G</SPAN>

H</SPAN>

I</SPAN>

J</SPAN>

K</SPAN>

L</SPAN>

1</SPAN>

Section 1</SPAN>

2</SPAN>

Year</SPAN>

From Date</SPAN>

To Date</SPAN>

# of days</SPAN>

hours</SPAN>

Service</SPAN>

Amount</SPAN>

Prorated Amount</SPAN>

Rate 1</SPAN>

Prorated Rate 1</SPAN>

Rate 2</SPAN>

Prorated Rate 2</SPAN>

3</SPAN>

2008</SPAN>

2008/12/22</SPAN>

2008/12/27</SPAN>

6</SPAN>

40</SPAN>

0.016393</SPAN>

$47,731.00</SPAN>

$782.48</SPAN>

$974.10</SPAN>

$15.97</SPAN>

$1,542.00</SPAN>

$25.28</SPAN>

4</SPAN>

2008</SPAN>

2008/12/28</SPAN>

2008/12/31</SPAN>

4</SPAN>

40</SPAN>

0.010929</SPAN>

$47,731.00</SPAN>

$521.65</SPAN>

$974.00</SPAN>

$10.64</SPAN>

$1,542.00</SPAN>

$16.85</SPAN>

5</SPAN>

2009</SPAN>

2009/01/01</SPAN>

2009/01/31</SPAN>

31</SPAN>

40</SPAN>

0.084932</SPAN>

$47,731.00</SPAN>

$4,053.87</SPAN>

$1,000.00</SPAN>

$84.93</SPAN>

$1,883.00</SPAN>

$159.93</SPAN>

6</SPAN>

2009</SPAN>

2009/02/01</SPAN>

2009/12/26</SPAN>

329</SPAN>

40</SPAN>

0.901370</SPAN>

$49,045.00</SPAN>

$44,207.68</SPAN>

$1,000.00</SPAN>

$901.37</SPAN>

$1,883.00</SPAN>

$1,697.28</SPAN>

7</SPAN>

2009</SPAN>

2009/12/27</SPAN>

2009/12/31</SPAN>

5</SPAN>

40</SPAN>

0.013699</SPAN>

$49,045.00</SPAN>

$671.85</SPAN>

$1,000.00</SPAN>

$13.70</SPAN>

$1,883.00</SPAN>

$25.79</SPAN>

8</SPAN>

9</SPAN>

Section 2</SPAN>

10</SPAN>

Year</SPAN>

Sum of Prorated Amount</SPAN>

Sum of Rate 1 (column J)</SPAN>

Sum of Rate 2 (column L)</SPAN>

Total of Rates</SPAN>

Total Calculated</SPAN>

Total from other source used for auditing</SPAN>

Difference between the 2 totals</SPAN>

11</SPAN>

2008</SPAN>

$0.00</SPAN>

$0.00</SPAN>

$0.00</SPAN>

12</SPAN>

2009</SPAN>

$0.00</SPAN>

$0.00</SPAN>

$0.00</SPAN>

13</SPAN>

Total</SPAN>

$0.00</SPAN>

$0.00</SPAN>

$0.00</SPAN>

$0.00</SPAN>

$0.00</SPAN>

$0.00</SPAN>

$0.00</SPAN>

<TBODY>
</TBODY>

 

[Weazel]

If I'm understanding correctly then maybe somthing like...

=SUMIF($A$3:$A$7,A11,$H$3:$H$7) in B11

 

[Christine365]

Thank you! It is working exactly the way I wanted. I really appreciate your help!