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

lestat666

New Member
Joined
Aug 10, 2015
Messages
3
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 NamesJanFebMar
Child 1's NameFormulaFormulaFormula
Child 2's NameFormulaFormulaFormula

<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.
 

Drrellik

Well-known Member
Joined
Apr 29, 2013
Messages
738
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
Joined
Aug 10, 2015
Messages
3
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 NamesJanFebMar
Child 1's NameFormulaFormulaFormula
Child 2's NameFormulaFormulaFormula

<tbody>
</tbody>


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

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top