Need Help efficiently creating Monthly totals from Sum formula in an Array using data in another worksheet in Excel 2007 & WinXP

[lestat666]

Using Excel 2007 and Windows XP

Here is what I have.

This is part of worksheet 1. (There are 10 grouping, all the way up to Child 10)
A B C D E F G
[TABLE="width: 5"]
<tbody>[TR]
[TD]1[/TD]
[TD]Child 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Child 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Dates[/TD]
[TD]Pd[/TD]
[TD]# of Days[/TD]
[TD]Amounts[/TD]
[TD]pd[/TD]
[TD]#of Days[/TD]
[TD]Amounts[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl67, width: 79"]7-Jan-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl67, width: 79"]14-Jan-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl67, width: 79"]21-Jan-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl67, width: 79"]28-Jan-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl67, width: 79"]4-Feb-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl67, width: 79"]11-Feb-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl67, width: 79"]18-Feb-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl67, width: 79"]25-Feb-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[/TR]
</tbody>[/TABLE]

Worksheet 1 has a Dates column for the 52 weeks of the year.
I want to array the monthly totals in Worksheet 2.

Here is a part of worksheet 2.

[TABLE="width: 300"]
<tbody>[TR]
[TD]Children's Names[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]Child 1's Name[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]Child 2's Name[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[/TR]
</tbody>[/TABLE]

So what I want to do is I want to sum the total for each month for 10 children. Is there a way to do this efficiently without having to enter 120 separate SUM formulas?

I have tried to use autofill, but that doesn't work because it increments the cell values by 1.... ie if there are 4 weeks in Jan and Feb and I enter SUM($D3:$D6) and I try to autofill the Feb Column, it fills it as SUM($D4:$D7) instead of SUM($D7:$D10).... Same Goes if I try to autofill Down using SUM(D$4:D$7).... It autofills as (E$4:E$8) rather than (G$4:G$8).

I also tried using range names.... but using autofill with these, just populates the exact same formula then and I also still have to create 120 range names. Albeit, this is still quicker than having to manually type in the cell values...


But I would think there still has to be a quicker way.

I also want to be able to edit the ranges at a future time, because the number of weeks in a month isn't always the same every year.

Any help is greatly appreciated.

 

[lestat666]

Bump.

 

[Drrellik]

Welcome to the Forum, depending on how you set up your columns have you looked at a Pivot table.

they can be tricky, but are very powerful and useful when dealing with tables.

 

[lestat666]

Welcome to the Forum, depending on how you set up your columns have you looked at a Pivot table.

they can be tricky, but are very powerful and useful when dealing with tables.

I haven't really used them too much.
I only really have basic knowledge of excel.

I just realized that my column labels didn't line up properly. Here is what it is supposed to look like.

Sheet 1
[TABLE="class: cms_table, width: 5"]
<tbody>[TR]
[TD][/TD]
[TD] A[/TD]
[TD] B[/TD]
[TD] C[/TD]
[TD] D[/TD]
[TD] E[/TD]
[TD] F[/TD]
[TD] G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Child 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Child 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Dates[/TD]
[TD]Pd[/TD]
[TD] # of Days[/TD]
[TD]Amounts[/TD]
[TD]pd[/TD]
[TD]#of Days[/TD]
[TD]Amounts[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="class: cms_table, width: 79"]
<tbody>[TR]
[TD="class: cms_table_xl67, width: 79"]7-Jan-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="class: cms_table, width: 79"]
<tbody>[TR]
[TD="class: cms_table_xl67, width: 79"]14-Jan-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="class: cms_table, width: 79"]
<tbody>[TR]
[TD="class: cms_table_xl67, width: 79"]21-Jan-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="class: cms_table, width: 79"]
<tbody>[TR]
[TD="class: cms_table_xl67, width: 79"]28-Jan-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="class: cms_table, width: 79"]
<tbody>[TR]
[TD="class: cms_table_xl67, width: 79"]4-Feb-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][TABLE="class: cms_table, width: 79"]
<tbody>[TR]
[TD="class: cms_table_xl67, width: 79"]11-Feb-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][TABLE="class: cms_table, width: 79"]
<tbody>[TR]
[TD="class: cms_table_xl67, width: 79"]18-Feb-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE="class: cms_table, width: 79"]
<tbody>[TR]
[TD="class: cms_table_xl67, width: 79"]25-Feb-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[TD][/TD]
[TD][/TD]
[TD]$$$[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2.. Need to use data in sheet one and sum the monthly totals in sheet2.
[TABLE="class: cms_table, width: 300"]
<tbody>[TR]
[TD]Children's Names[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]Child 1's Name[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula
[/TD]
[/TR]
[TR]
[TD]Child 2's Name[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[/TR]
</tbody>[/TABLE]


How would I use a pivot table to accomplish what I need to do?