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
<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.
<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.
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 |
<tbody> </tbody> | $$$ | $$$ | |||||
4 |
<tbody> </tbody> | $$$ | $$$ | |||||
5 |
<tbody> </tbody> | $$$ | $$$ | |||||
6 |
<tbody> </tbody> | $$$ | $$$ | |||||
7 |
<tbody> </tbody> | $$$ | $$$ | |||||
8 |
<tbody> </tbody> | $$$ | $$$ | |||||
9 |
<tbody> </tbody> | $$$ | $$$ | |||||
10 |
<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.