Subtotals, Totals and GrandTotals
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Subtotals, Totals and GrandTotals

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Chicago
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How does one programatically create subtotals, totals, and then GrandTotals.

    I have data that once is entered I want to create the totals. I am keying off of the fact that in column a, there will either be numbers which represent skus, or the word Subtotal, Total, Grand Total.

    Does anyone have a looping construct that will automatically create the sums in those indicated rows? The logic being that Subtotals are the totals of the prices above it. The Total is the sum of the just the subtotals above it, and finally the Grand Total is the sum of all the totals.

    There can be multiple totals.

    A | B

    123 | 12.5
    234 | 23.5
    345 | 25.8
    Subtotal abc

    344 | 12.3
    365 | 23.2
    Subtotal bcd
    Total = YYY (abc+bcd)

    456 | 23.3
    989 | 12.1
    Subtotal cde

    567 | 34.9
    890 | 17.5
    Subotal def
    Total xyz (cde +def)
    Grand Total XYXY (all totals)

    Thanks

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try using the subtotal method. Data|Subtotals
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Chicago
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I can't. Has to be done programatically since I am creating the spreadsheet from a setup sheet. In other words the individual sets up a set of skus and they want to create subtotals and totals. These are different for each individual. Of course once I figure out these totals I will be able to use them in other sheets too.

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The subtotal method can also be done in VBA. Look up the subtotal function.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  5. #5

    Join Date
    Dec 2003
    Location
    Denver, CO
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subtotals, Totals and GrandTotals

    Ok, if a visual basic code can be written for this type of scenario, I have the same scenario, can it be adapted easily or deleting or adding a section? I have a need for a subtotal, total system that will auto-adjust if a section is added or deleted. (Salespersons that leave and sections are combined or salespeople added in surplus and sections are added). I am trying to create something for a group of people less excel savvy.

    Please help me out. I think I am getting close

  6. #6
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,962
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Subtotals, Totals and GrandTotals

    Jimijon, don't forget your good friend - the macro recorder!
    Greg

    Home: XL 2003, 2007, 2010, and 2013 on Windows 7
    Work: XL 2013, 2016 on Windows 10
    Please use CODE tags - especially for longer excerpts of code.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com