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

#### lestat666

##### New Member
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
1Child 1Child 2
2DatesPd# of DaysAmountspd#of DaysAmounts
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.

Bump.

#### Drrellik

##### Well-known Member
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

##### New Member
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
1Child 1Child 2
2DatesPd # of DaysAmountspd#of DaysAmounts
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?

Last edited:

1,082,344
Messages
5,364,806
Members
400,814
Latest member
gangstar67

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...