Weekly and Monthly Subtotals
 

Hi,

I have a spreadsheet with six columns. They are Month, Day (day of the
month), Weekly Number (this relates to how many weeks in a month), Total
(daily sales Total), # of Sales and Daily Average. I created a subtotals
line that sums the sales for a given week. I did this by having excel sum
the total on a change in Week Number. This works fine. Now I want to sum
the sales for the month. I obviously don't want to include the weekly
subtotals in these figures since they can overlap per month. Can someone
help with creating a second subtotal line that sums the monthly sales but
excludes the weekly subtotals?

Thanks,



Month Day Week # Total # of Sales Daily Average
June 1 1 $973.06 2 $486.53
2 1 $1,067.05 3 $355.68
3 1 $- 0
1 Total $2,040.11 5 $408.02
6 2 $1,018.80 1 $1,018.80
7 2 $648.65 4 $162.16
8 2 $6,770.90 4 $1,692.73
9 2 $3,538.00 2 $1,769.00
10 2 $3,030.65 2 $1,515.33
2 Total $15,007.00 13 $1,154.38







--
Chuck W

Try...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),0,1)))

Note that subtotals within the range will be ignored.

Hope this helps!

In article ,
ChuckW wrote:

Hi,

I have a spreadsheet with six columns. They are Month, Day (day of the
month), Weekly Number (this relates to how many weeks in a month), Total
(daily sales Total), # of Sales and Daily Average. I created a subtotals
line that sums the sales for a given week. I did this by having excel sum
the total on a change in Week Number. This works fine. Now I want to sum
the sales for the month. I obviously don't want to include the weekly
subtotals in these figures since they can overlap per month. Can someone
help with creating a second subtotal line that sums the monthly sales but
excludes the weekly subtotals?

Thanks,



Month Day Week # Total # of Sales Daily Average
June 1 1 $973.06 2 $486.53
2 1 $1,067.05 3 $355.68
3 1 $- 0
1 Total $2,040.11 5 $408.02
6 2 $1,018.80 1 $1,018.80
7 2 $648.65 4 $162.16
8 2 $6,770.90 4 $1,692.73
9 2 $3,538.00 2 $1,769.00
10 2 $3,030.65 2 $1,515.33
2 Total $15,007.00 13 $1,154.38

Domenic,

Thanks for your help. I am a novice excel user and am trying to figure out
how to do what you suggested. I got the weekly subtotal by using the
pulldowns. How would I do the monthly ones?

Thanks,

--
Chuck W


"Domenic" wrote:

Try...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),0,1)))

Note that subtotals within the range will be ignored.

Hope this helps!

In article ,
ChuckW wrote:

Hi,

I have a spreadsheet with six columns. They are Month, Day (day of the
month), Weekly Number (this relates to how many weeks in a month), Total
(daily sales Total), # of Sales and Daily Average. I created a subtotals
line that sums the sales for a given week. I did this by having excel sum
the total on a change in Week Number. This works fine. Now I want to sum
the sales for the month. I obviously don't want to include the weekly
subtotals in these figures since they can overlap per month. Can someone
help with creating a second subtotal line that sums the monthly sales but
excludes the weekly subtotals?

Thanks,



Month Day Week # Total # of Sales Daily Average
June 1 1 $973.06 2 $486.53
2 1 $1,067.05 3 $355.68
3 1 $- 0
1 Total $2,040.11 5 $408.02
6 2 $1,018.80 1 $1,018.80
7 2 $648.65 4 $162.16
8 2 $6,770.90 4 $1,692.73
9 2 $3,538.00 2 $1,769.00
10 2 $3,030.65 2 $1,515.33
2 Total $15,007.00 13 $1,154.38

I've misunderstood what you're looking for. See if the following
helps...

1) First, make sure that each cell in Column A contains the month for
the corresponding day...

a) Select/highlight Column A

b) Edit Go To Special Blanks Ok

c) Press =

d) Hit the 'Up Arrow' key

e) Press CONTROL+ENTER

2) Then, use SUBTOTAL to give you your monthly subtotals...

a) Select your data, including your headers

b) Data Subtotals

At each change in: Month

Use function: Sum

Add subtotal to: check the appropriate header/column

Replace current subtotals: uncheck this option

Click Ok

Hope this helps!

In article ,
ChuckW wrote:

Domenic,

Thanks for your help. I am a novice excel user and am trying to figure out
how to do what you suggested. I got the weekly subtotal by using the
pulldowns. How would I do the monthly ones?

Thanks,

--
Chuck W