Month to date calculations
 

Hi,

The spreadsheet I am working is one that is done daily with a weekly recap
of a total of certain codes. What I want to do is have the Month to Date
total of the codes be automatic without having to link it to all the other
weekly spreadsheets.

For example: A1=1 (for week 1, 2 for week 2, etc.)

=if (A1=1,a2+?,?) (A2 would represent the total of that code for that week)

My sheet will total the codes for that week, but how do I automate adding
Week 1's totals to Week 2's totals etc. for a Month to Date total?

Right now, I am having to manually add the current week with the past weeks
totals to get the Month to Date total.

Any ideas?

Thanks, CP

Month to date calculations
 

=SUM($A$1:A1)

copied down

will sum A1, then A1:A2 then A1:A3

is that what you want?

--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"CP" wrote in message
...
Hi,

The spreadsheet I am working is one that is done daily with a weekly recap
of a total of certain codes. What I want to do is have the Month to Date
total of the codes be automatic without having to link it to all the other
weekly spreadsheets.

For example: A1=1 (for week 1, 2 for week 2, etc.)

=if (A1=1,a2+?,?) (A2 would represent the total of that code for that
week)

My sheet will total the codes for that week, but how do I automate adding
Week 1's totals to Week 2's totals etc. for a Month to Date total?

Right now, I am having to manually add the current week with the past
weeks
totals to get the Month to Date total.

Any ideas?

Thanks, CP

Month to date calculations
 

Hi

It is a little unclear from your posting as to how your data is set out.
You say you want to add data from Other sheets, to give a month to data
total. What about part weeks, to make up a month, or are you working on
13 x 4 week periods rather than months?

Could you post some more detail about how each sheet is set out?


--
Regards

Roger Govier





CP wrote
Hi,
The spreadsheet I am working is one that is done daily with a weekly
recap of a total of certain codes. What I want to do is have the
Month to Date total of the codes be automatic without having to
link it to all the other weekly spreadsheets.
For example: A1=1 (for week 1, 2 for week 2, etc.)
=if (A1=1,a2+?,?) (A2 would represent the total of that code for
that week) My sheet will total the codes for that week, but how do I
automate adding Week 1's totals to Week 2's totals etc. for a Month
to Date total? Right now, I am having to manually add the current
week with the past weeks totals to get the Month to Date total.
Any ideas?
Thanks, CP

Month to date calculations
 

Hi Peo,

Thanks for your response. The workbook consists of 6 pages: one for each
day, then a recap for the week. It is done every week. The daily sheets
will calculate the days activities based on "codes" for each activity. Ex.
"C" = "Cold Call". So it will count up how many "C's" for the day and then
for the week on the recap. That's the easy part.

What I want it to do is 'save' the weekly count into columns for week 1,
week 2, week 3, and week 4 to get a cumulative total for the month. Say the
Weekly Recap count 10 "C's" for week 1, then 2 "C's" for week 2. When I do
the workbook for week 2, the column for the week's totals of "C's" will say 2
and the 10 from week 1 will go away. So I have to manually add the 2 to the
10 that I've manually entered into the MTD column next to the Week column. I
want to be able to 'save' the 10, then the 2 and so on so that process can be
automatic.

I hope that clarifies my question better.

Thanks, CP

"Peo Sjoblom" wrote:

=SUM($A$1:A1)

copied down

will sum A1, then A1:A2 then A1:A3

is that what you want?

--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"CP" wrote in message
...
Hi,

The spreadsheet I am working is one that is done daily with a weekly recap
of a total of certain codes. What I want to do is have the Month to Date
total of the codes be automatic without having to link it to all the other
weekly spreadsheets.

For example: A1=1 (for week 1, 2 for week 2, etc.)

=if (A1=1,a2+?,?) (A2 would represent the total of that code for that
week)

My sheet will total the codes for that week, but how do I automate adding
Week 1's totals to Week 2's totals etc. for a Month to Date total?

Right now, I am having to manually add the current week with the past
weeks
totals to get the Month to Date total.

Any ideas?

Thanks, CP

Month to date calculations
 

Hi Roger,

Thanks for your reply. Please see my reply to Peo for more clarification.
What I want to do is to get cumulative totals without having to link to other
workbooks for them.

Thanks, CP

"Roger Govier" wrote:

Hi

It is a little unclear from your posting as to how your data is set out.
You say you want to add data from Other sheets, to give a month to data
total. What about part weeks, to make up a month, or are you working on
13 x 4 week periods rather than months?

Could you post some more detail about how each sheet is set out?


--
Regards

Roger Govier





CP wrote
Hi,
The spreadsheet I am working is one that is done daily with a weekly
recap of a total of certain codes. What I want to do is have the
Month to Date total of the codes be automatic without having to
link it to all the other weekly spreadsheets.
For example: A1=1 (for week 1, 2 for week 2, etc.)
=if (A1=1,a2+?,?) (A2 would represent the total of that code for
that week) My sheet will total the codes for that week, but how do I
automate adding Week 1's totals to Week 2's totals etc. for a Month
to Date total? Right now, I am having to manually add the current
week with the past weeks totals to get the Month to Date total.
Any ideas?
Thanks, CP

Month to date calculations
 

Hi CP

I would be inclined to keep all the data on a single sheet.
Supposing you have a header in row 1, with Date in A1 and Calls in B1
A2 onward conatins a list of dates, B2 onwards has "C" in some of the
cells.

In D1 Enter Week1 and copy across through E1:H1 so you have Week1
through Week5, enter Month in I1
In cell D2 enter the start of the first week you are interested in.
In E2 enter =D2+7 and copy across through F2:H2. In I2 enter =D2
In D3 enter
=SUMPRODUCT(--($A$2:$A$1000=D2),--($A$2:$A$1000<E2),--($B$2:$B$1000="C"))
Copy across through cells E3:H3
In cell I3 enter
=SUMPRODUCT(--(MONTH($A$2:$A$1000)=MONTH(I2)),--($B$2:$B$1000="C"))

Now, all you have to do is alter the date in D2, and you will have the
numbers for each week in the month, and the cumulative for the month.

Play around with this idea to find what suits you. Change the ranges to
suit, but do not use whole columns as a range, Sumproduct doesn't handle
whole columns like A:A.


--
Regards

Roger Govier


CP wrote
Hi Roger,
Thanks for your reply. Please see my reply to Peo for more
clarification. What I want to do is to get cumulative totals
without having to link to other workbooks for them.
Thanks, CP
"Roger Govier" wrote:
Hi
It is a little unclear from your posting as to how your data is
set out. You say you want to add data from Other sheets, to give
a month to data total. What about part weeks, to make up a month,
or are you working on 13 x 4 week periods rather than months?
Could you post some more detail about how each sheet is set out?
--
Regards
Roger Govier
CP wrote
Hi,
The spreadsheet I am working is one that is done daily with a weekly
recap of a total of certain codes. What I want to do is have the
Month to Date total of the codes be automatic without having to
link it to all the other weekly spreadsheets.
For example: A1=1 (for week 1, 2 for week 2, etc.)
=if (A1=1,a2+?,?) (A2 would represent the total of that code for
that week) My sheet will total the codes for that week, but how do I
automate adding Week 1's totals to Week 2's totals etc. for a Month
to Date total? Right now, I am having to manually add the current
week with the past weeks totals to get the Month to Date total.
Any ideas?
Thanks, CP