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 <[email protected]>,
ChuckW <[email protected]> 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 <[email protected]>,
> ChuckW <[email protected]> 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 <[email protected]>,
ChuckW <[email protected]> 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