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

1	Child 1				Child 2
2	Dates	Pd	# of Days	Amounts	pd	#of Days	Amounts
3	7-Jan-13 <tbody> </tbody>			$$$			$$$
4	14-Jan-13 <tbody> </tbody>			$$$			$$$
5	21-Jan-13 <tbody> </tbody>			$$$			$$$
6	28-Jan-13 <tbody> </tbody>			$$$			$$$
7	4-Feb-13 <tbody> </tbody>			$$$			$$$
8	11-Feb-13 <tbody> </tbody>			$$$			$$$
9	18-Feb-13 <tbody> </tbody>			$$$			$$$
10	25-Feb-13 <tbody> </tbody>			$$$			$$$

<tbody>
</tbody>

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.

Children's Names	Jan	Feb	Mar
Child 1's Name	Formula	Formula	Formula
Child 2's Name	Formula	Formula	Formula

<tbody>
</tbody>

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

	A	B	C	D	E	F	G
1	Child 1				Child 2
2	Dates	Pd	# of Days	Amounts	pd	#of Days	Amounts
3	7-Jan-13 <tbody> </tbody>			$$$			$$$
4	14-Jan-13 <tbody> </tbody>			$$$			$$$
5	21-Jan-13 <tbody> </tbody>			$$$			$$$
6	28-Jan-13 <tbody> </tbody>			$$$			$$$
7	4-Feb-13 <tbody> </tbody>			$$$			$$$
8	11-Feb-13 <tbody> </tbody>			$$$			$$$
9	18-Feb-13 <tbody> </tbody>			$$$			$$$
10	25-Feb-13 <tbody> </tbody>			$$$			$$$

<tbody>
</tbody>

Sheet 2.. Need to use data in sheet one and sum the monthly totals in sheet2.

Children's Names	Jan	Feb	Mar
Child 1's Name	Formula	Formula	Formula
Child 2's Name	Formula	Formula	Formula

<tbody>
</tbody>

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