+ Reply to Thread
Results 1 to 4 of 4

Weekly and Monthly Subtotals

  1. #1
    ChuckW
    Guest

    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

  2. #2
    Domenic
    Guest

    Re: Weekly and Monthly Subtotals

    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 <794CD49E-66B4-439D-A29E-4CBB42F5E751@microsoft.com>,
    ChuckW <haji@hotmail.com> 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


  3. #3
    ChuckW
    Guest

    Re: Weekly and Monthly Subtotals

    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 <794CD49E-66B4-439D-A29E-4CBB42F5E751@microsoft.com>,
    > ChuckW <haji@hotmail.com> 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

    >


  4. #4
    Domenic
    Guest

    Re: Weekly and Monthly Subtotals

    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 <3CC408F9-299E-4FAE-9E1B-481C60FEE5FD@microsoft.com>,
    ChuckW <haji@hotmail.com> 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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1